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?
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.