Hi All,

I want to store Student Name & Birth date in the database. How should I store it into database so that I can retrieve it back evenif the system date format is changed(It should retrieve record for dd/MM/yyyy date format also i.e. not only for default date formats). Any clue?

Recommended Answers

Answered by QVeen72 104 in a post from

Hi,

It is always a Good Idea to store Dates in "Julian" style which is Numeric Long type. If stored in this way, whatever be the Local Regional setting of the computer u can get it back to required format. while storing use:

RST("DateField") = CLng(CDate(txtDate.Text))

To …

Jump to Post
Answered by QVeen72 104 in a post from

Hi,

Yes u cand do it. Julian Date Represents both date and Time, The Integral part (or the floor) is Date and the Decimal part is the Time, Precising up to 1 MilliSecond.
And u can do with a CDbl. U had asked for date, so i told u …

Jump to Post
Answered by QVeen72 104 in a post from

Hi,

In Database if u have used double to store the record, precision wud have formatted to 4 places. so use this :

SELECT * FROM Results WHERE SettingsID=7 AND TestDateTime=39303.5540


REgards
Veena

Jump to Post

All 11 Replies

Hi,

It is always a Good Idea to store Dates in "Julian" style which is Numeric Long type. If stored in this way, whatever be the Local Regional setting of the computer u can get it back to required format. while storing use:

RST("DateField") = CLng(CDate(txtDate.Text))

To Retrieve Back use:

txtDate.Text = Format(RST("DateField"),"dd/MMM/yyyy")

But this has got its own draw back, Reporting gets difficult, in db all the records look like numbers. If using CR, u can create formula to convert it back to date...


Regards
Veena

Thanks Veena. I'll try.

It works fine. Is it possible to store Time also in the same filed i.e. Birth date & time in one database field using "Julian" style.
Thanks.

I got it. I have used CDbl instead of CLng.

RST("DateField") = CDbl(CDate(txtDate.Text))

Thanks for valuable information.

Hi,

Yes u cand do it. Julian Date Represents both date and Time, The Integral part (or the floor) is Date and the Decimal part is the Time, Precising up to 1 MilliSecond.
And u can do with a CDbl. U had asked for date, so i told u to use Long.

Regards
Veena

Hi,

Thanks a lot!!!

Hi,

New problem. I'm unable to search date in database. In MS Access I've tried by creating query :

SELECT * FROM Results WHERE SettingsID=7 AND TestDateTime=39303.5539930556

This query doesn't return a row, but it is there in the table.

Hi,

In Database if u have used double to store the record, precision wud have formatted to 4 places. so use this :

SELECT * FROM Results WHERE SettingsID=7 AND TestDateTime=39303.5540


REgards
Veena

Hi,

In database TestDateTime field has Auto decimal places.

Hi,

So has it saved upto 10 digits in decimal..?
coz, here in my system even if it is "Auto", it saves upto 4 dec places. Browse the data and check..

REgards
Veena

Hi,

In my DB Decimal places was Auto by default and the results stored are having 10 dec. places.
One more I observed - if I query the DB as

SELECT * FROM Results WHERE TestDateTime=39303.5539930556

It returns the row. ALso

SELECT * FROM Results WHERE SettingsID=7
returns the records.
But combining two doesnot return the row evenif it is there in the DB.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.