Getting Dates into Access
By Toby Allen
Monday, February 02, 2004
I'm currently working on a project and like many people I'm using Microsoft access through ADO as my Database. I dont want to get into a dicussion about the relative merits of Access over or below mysql, mssql, msde, paradox etc. Lets just assume I've done my homework, and decided that Access is the best for what I'm doing.
However there is one thing that is the bain of every programmers life no matter what they are doing.
Dates
If you are in America it's mm/dd/yyyy if your in Ireland and the UK its dd/mm/yyyy and no doubt if your somewhere else in the world its something even more bizarre. Now usually this isnt really a problem, as long as your windows locale is set correctly, most programs should deal with dates correctly. Except Access!
Access stubornly refuses to take a look at your system settings and randomly chooses which format to use. If its ambiguous which is the day and which is the month in xx/xx/2004 access will assume mm/dd/yyyy, but if on or the other is above 12 then that will be used as the day and the other month. This is not ideal for a realiable application.
Now no doubt there is a setting somewhere that you can tell access what format to use, but I cant find it, and to be honest I couldnt be bothered looking, because even if I get it right, what chance do my users have of getting it right. What I need is a completely unambiguous method of entering dates. In both mssql and mysql you can use yyyymmdd and it works fine. But no in access. However after a bit of googling, I found the format that works for access (well on my machine at least).
YYYY-MM-DD
I hope this is useful to someone, once google finds this.
--
An extra note: In my project, I'm adding several thousand small records to a table and boy does the database grow. I added about 30,000 records to a table, and the database size rose to a whopping 24MB. I then compacted the database and it dropped to 4MB. See my up coming article on Compacting your database from Delphi.