Converting date to number

I have been told that the best way to store dates in an Access DB is to convert the date to a number , write it and then when the date is required find it and convert it back to whatever date format is on the machine and display it.
What is your opinions on this and if you think that this is a good practice perhaps you might have some code to demonstrate it.

Hope to hear from you experts

Who is Participating?
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
edel2000ie, dd/mm/yyyy is fine except that you should ALWAYS coerce the date into the yyyy-mm-dd format when inserting/updating or when using a date as a query parameter. Access has this annoying habit of treating all dates used as query values in mm/dd/yy regardless of the locale setting of the machine. Therefore a query such as

Select * From MyTable Where StartDate >= #02/05/2002#

May well be interpreted wrongly depending on the date settings of the machine, access will always read this as Feb 5th rather than 2nd May.

Select * From MyTable Where StartDate >= #2002-05-02#

Will always be treated as may 2nd. You can do this in VB using the format command to simply transform the date into the appropriate format:

"Select * From MyTable Where StartDate >= #" & Format(MyDateVar,"yyyy-mm-dd") & "#"

TimCotteeHead of Software ServicesCommented:
That will work, though it can be frustrating if you have different methods of accessing the data as you will have to cope with this numeric (double data type) storage in each application. Personally using access I always ensure that I write dates to it using "yyyy-mm-dd" as this ensures that the date is converted when stored to the correct date format, when reading it that is fine too. The date is probably stored internally as a double rather than as a string so using this method preserves the simple date handling functions and eliminates the need to code around storing it as a number yourself.
It depends on the application and what you're using the date for. If it is just for display purposes, then the above sounds reasonable. But in many business applications you have to do a lot of calculations on dates, and it is often easier to establish a standard date format and stick with it. I like yyyy-mm-dd because it sorts into chronoligical order.
Depending on the application again, you can reset the workstation's short date format as follows:

' Reset system short date format to yyyy-MM-dd
newFormat = "yyyy-MM-dd"
Call SetLocaleInfo(LCID, LOCALE_SSHORTDATE, newFormat)

This is acceptable in a company environment, but may not be for the general public.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

edel2000ieAuthor Commented:
thanks gron and TimCottee Yes there will be a lot of date calculations. The generally accepted date format in most companies here is dd/mm/yyyy. So if I develop my app in this date format and coerce the date format of the client PC to this date then I should have no problem. I think that setting the date will be acceptable to the companies that the app will be developed for. They will be informed before they buy the product.

If anyone one has more ideas please let me know

If you are planning on reporting from your database using a 3rd party reporting tool, such as crystal reports, all of these fields will be treated as numerics. You will lose the ability to format the displayed date (although you can do this using formulas). You will also not be able to group things by month or year etc..

I personally prefer using the date data type, it may take up slightly more space, but in the long run, it's more consistant.
edel2000ieAuthor Commented:
Yes I do intend to use Cryatal Reports. Good point. Thank you.

Do you format each date before you write it to the database and then format it again when you read it.
rstRecSet!StartDate = format(DTPicker1.value,"yyyy-mm-dd" )

edel2000ieAuthor Commented:
sorry TimCottee got our messages crossed
edel2000ieAuthor Commented:
Tim Cottee

OK TimCottee now we are getting somewhere.
So let me run through a senario that I think should be safe from what you have told me.

Before I write a date to Access format it to yyyy-mm-dd.

Before I display a date I format it to what ever the user wants, in my case dd/mm/yyyy.

If the user selects a date in a DTPicker for a query, I format it to yyyy-mm-dd before I put it in the SQL statement.

I select a short date format for my Access date fields.

It does not matter what date locale settings are on the user PC. If I open my database on the users machine and they have date settings of dd-mm-yyyy, will I see the date in format dd-mm-yyyy or yyyy-mm-dd? Does it matter if the date is stored as a double. Is writing it in yyyy-mm-dd format just making sure that Access is able to convert it properly to a double?

Have I got it right?

If this is the way to go then I am off to write an app with confidence. In the past I have had a few problems with dates in Access. I worked around them but this app is pretty big so I want it dead right.

Your help is much appreciated


Since you plan to use Crystal Reports, knowing the format of dates is important.  CR expects dates to be in a specific format (yyyy-mm-dd).

If the users are going to be entering dates, I suggest you use a calendar dropdown otherwise the machine locale format may interfere and also users will enter dates in different formats.  We have an Access application used by 5-600 people where dates are critical.  If a user has changed his local format the program may not allow him to enter a date. We use dd/mmm/yyyy for entry but an occasional user will change his local format to be dd-mmm-yyyy and the date can't be entered until we change him back.

In our recent rewrite (not yet installed) we chose to use a Sheridan calendar control and not to allow the user to type the date in simply to prevent these problems.

good luck
edel2000ieAuthor Commented:
Hi mlmcc
I am using a DateTime Picker to select the dates. The format I am using for this is dd/mm/yyyy. When the user selects a date I will convert it to yyyy-mm-dd and write it to DB or add it to SQL statement.

If I keep all dates in the DB as yyyy-mm-dd then I will be fine with CR, is this right?

Can you see any more problems that I may have?

My app has 75 forms and about 35 CR reports, many with dates, and I have had no problems with yyyy-mm-dd. Another alternative for date entry is the Masked Edit Box. In many cases, for example when the year and date are predetermined and all the user has to do is enter the day, you can enter the year-month and position the cursor at the day.
What I meant earlier when I said that this formats sorts in chronological order, I should have specified that even as text or string format it sorts correctly. The other date formats don't.
just looking back on your question...

is your freind suggesting that you convert the date to a numeric, and write it to an integer/long database field ??

If so, CR will always treat this as a numeric (unless you write a formula to convert it)

If your database uses date fields, then CR will treat the field correcly regardless of what format you pass in.. as long as it's valid.

PS. why does your friend suggest converting to a number ?? just for the sake of saving a few bytes ???
edel2000ieAuthor Commented:
I think the philosophy behind my friend's idea of using a long field to hold dates was that they could not be interferred with regardless of locale settings or Access querks. The CR approach was not considered in depth.

this masked edit approach is a very good idea. I think I will try the DateTimePicker first and see how it goes.

Well Thanks everyone, you have helped me tremendously.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.