How to store & retrieve dates from MS access databse

[kshrini]

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?

[QVeen72]

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

[kshrini]

Thanks Veena. I'll try.

[kshrini]

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.

[kshrini]

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

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

Thanks for valuable information.

[QVeen72]

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

[kshrini]

Hi,

Thanks a lot!!!

[kshrini]

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.

[QVeen72]

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

[kshrini]

Hi,

In database TestDateTime field has Auto decimal places.

[QVeen72]

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

[kshrini]

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.