The Old Joel on Software Forum: Part 4 (of 5)

Dates with MS Access

My regional settings has short date format set to dd/mm/yy, so today's date would be stored as 30-03-04. If a two digit year is entered, is is understood to be in the range 1930 to 2029.

I suddenly found while testing a piece of software I am doing that all logins in today's date were not being recorded. On opening the database, I saw that today's date was being stored as 04-03-30.

30-03-04 was being converted to 04-03-30, as a result all of those (fictitious employees) who logged in today in my test environment were shown to have logged in on the 4th of March, 1930. I ran the program in the debug mode. Everything was fine. I was sending in the correct date, the correct time, the correct date time, both for login as well as logout.

While the login date was being wrong stored, the logout date showed up correctly because over there the mechanism of storage used was different. For a login, I was doing an insert into the database, while for a logout it was a recordset I fetched and updated (didn't fire the udpate query, made a recordset, and called rs.Update on it).

I was puzzled. I was so terribly confused. Even the debug window reported that the SQL string I composed was correct. I took the query to the data environment designer and it returned the same results. Then while I sat thinking mildly over the problem, my cursor was in the immediate window. I wasn't moving my mouse. My eyes quicky caught a glance at what the tooltip in the immediate window read. Co-incidentally, when my eyes met the cursor it was just above the value #30-03-04#, and I had a hearty laugh. The tooltip read

#30-03-04 5:28 00 PM# = 04-03-30 5:28 00 PM

I was so relieved.

When I packaged the dates in the # character, 30-03-04 was being converted to 04-03-30. I immediately changed the delimiters to apostrophe to eliminate the bug and its working now. I thought I'd share this quirk with everyone here.

Sathyaish Chakravarthy
Tuesday, March 30, 2004

Methinks that your problem isn't actually solved. For Access to recognize a parameter as DateTime you need to surround it with #'s. The reason apos "Fixed" it, I think, is that access no longer sees it as a DateTime.

Wunderkind
Tuesday, March 30, 2004

I understand what you are saying, but how could that be possible? Could it be possible even if I declared/defined the field as a DateTime with the DateFormat set to ShortDate.

Sathyaish Chakravarthy
Tuesday, March 30, 2004

Frankly, I'm surprised that Access is actually accepting the data without #'s and not complaining about a type mismatch.

Access does has some strange quirks, though. One that I noticed is that when working in the application (as opposed to using the Jet DB Engine) you have to use "%" as the wildcard character EXCEPT when you're doing a UNION query, in which case you need to use an "*"

Wunderkind
Tuesday, March 30, 2004

Certainly it used to be the case that while most of access would obey the windows locality setting for how to format and accept dates, the SQL interface would only accept US format.

Which is a bit rude...

I don't know if this was ever fixed, because I stopped being an Access developer.

katie lucas
Tuesday, March 30, 2004

A date with a woman would be better.

Tuesday, March 30, 2004

Access and VB and VBA all interpret a date/time within # delimiters in the standard us date format of mm/dd/yyyy

Think of the # delimiters as being a shortcut for defining a constant and forcing it to be date/time.

When you assign a string value to a date variable or column, all VB derived languages will interpret the string using the system locale.

Bottom line:

- NEVER assume anything about the system locale. In code, convert string values to dates as soon as possible and only ever pass dates around as variables in your app.

- ALWAYS explicitly format a date if you are cramming it into an SQL string. If you don't, it will be formatted using the system locale, but interpreted using the US locale.

This behaviour is not a bug or quirk, it has been documented in every version of Access and Visual Basic up to VB6. (I'm not sure about .NET)

Craig
Tuesday, March 30, 2004

"standard us date format of mm/dd/yyyy"

That should be "standard US date format"...

I need another coffee.

Craig
Tuesday, March 30, 2004

Just to add something else to think about, I've noticed that it sometimes doesn't seem to matter what format you use to actually enter a date, Access will do its best to figure out if it might be a valid date. For example, 30/12/2000 is clearly not valid if assuming mm/dd/yyyy, but Access doesn't seem to care. It just carries right on making the internal assumption that you meant dd/mm/yyyy (or, equivalently, that you meant 12/30/2000). Of course things get even wilder if using 2-digit years.

I try to always talk people into letting me set up input masks of the form 'mmm d, yyyy' (Dec 12, 2000) or other 'text for month, 4 digits for year) formats. I figure the slight pain they experience when entering the date is justified given the freedom from ambiguity. Of course, this is Canada, where the old standard (still taught in many schools and used by older folk) is dd/mm/yy, the common standard in many businesses and professions is mm/dd/yy and the official standard (I think; I see it on all my government documents) is yyyy-mm-dd.

Ron Porter
Tuesday, March 30, 2004

I think I heard that if you write out dates in yyyy-mm-dd in Sql Server it will always get it right, no matter the local setting for date formatting. I don't know if that works for Access or not. I've always liked yyyy-mm-dd format because it sorts into the right order as a string (if you were to put a date in a filename, for instance).

Keith Wright
Tuesday, March 30, 2004

Just to reinforce what Ron Porter said: with MS Access, the way to go is to use an unambigious date format - I always use "mmm-dd-yyyy" myself. Do this in both the data entry screen and anywhere in the code. I am also in Canada, and have to deal with dd/mm/yy (which I think is more common in Canada) vs. mm/dd/yy (which seems to be the USA standard) issue. I just don't want the hassle of having to worry about my code or my users misunderstanding the date if day of the month is 1 to 12.

Harlequin
Tuesday, March 30, 2004

My philsopy on dates is never force the order on the date format on the user. If they want m/d/y or d/m/y fine (I am in canada too) but use m/d/y.

So basically i do the following:
- Get date order from Locale Settings
- create two masks - one for entry (two digit month),
one for display (three char month)
- force the entry of four digit years

The two masks work great - it is easier to enter for the user and the display mask gives feedback to the user that they entered the right date. Also having a month calender drop down is much appreciated by the users.

As far as SQL server / Access is concerned I have a function that will format / wrap the dates properly for use in SQL strings.

SQL server - always 'yyyy-mm-dd' and Access always #mm/dd/yyyy#

DJ
Tuesday, March 30, 2004

DJ,

<quote>
SQL server - always 'yyyy-mm-dd' and Access always
#mm/dd/yyyy#
</quote>

yyyy-mm-dd, despite popular opinion, is not a foolproof format for SQL Server - it won't work if DATEFORMAT is set to YDM for example. YYYYMMDD is a better option (see the SQL Server help files for other options as well).

Seeya

Tuesday, March 30, 2004

Sathyaish,

The rules in Access are very simple. Simple, but very confusing.

They are as follows:

1) When in SQL view (or when executing queries from VBA / VB ect), dates should be entered as #mm/dd/yyyy#
2) When in Design view, dates should be entered as #whatever your local date format is#

In Australia, which has a local date format of dd/mm/yyyy, switching from SQL to Design view (or vice versa) will change what the date looks like. Again, as I said, simple - but very confusing.

Seeya

Tuesday, March 30, 2004

Ron Porter,

<quote>
I try to always talk people into letting me set up input masks of the form 'mmm d, yyyy' (Dec 12, 2000) or other 'text for month, 4 digits for year) formats.
</quote>

A few more stable option is to use a control (like the MS datetime picker) that returns Dates rather than Strings.

Seeya

Tuesday, March 30, 2004

Harlequin,

<quote>
Just to reinforce what Ron Porter said: with MS Access, the way to go is to use an unambigious date format - I always use "mmm-dd-yyyy" myself.
</quote>

I would suggest, the best solution (if using VB) is to follow Commandment XV from http://www.vb-faq.com/vb_commandments.asp . Means you don't have to worry about silly little details like date formats for different database platforms.

Seeya

Tuesday, March 30, 2004