Date literals in SQL and VBA

  • Thread starter Thread starter Tokyo Alex
  • Start date Start date
T

Tokyo Alex

Dear all,

I've very often seen here and in other locations that if you want to pass a
date literal to Jet/ACE it must be in US format, i.e. #mm/dd/yyyy#.

However, I have a small tool I use (that I made before I read this) that
passes it as #yyyy/mm/dd# which works fine. This is my PCs location setting,
as well.

So I did a couple of tests, both writing SQL directly into the query builder
and passing SQL statements from VBA using OpenRecordset and variables, and it
seemed to work fine regardless of what date format I chose (US, UK, ISO).
The only time it choked was if I gave it a two-digit year.

I use Access 2007 SP2.

Does anyone know if this is a new thing that was designed into Access 2007?
Or am I just lucky? Or has my PC been taken over by friendly aliens who are
actually doing the hard work for me?

Cheers,
Alex.
 
Tokyo said:
Dear all,

I've very often seen here and in other locations that if you want to pass a
date literal to Jet/ACE it must be in US format, i.e. #mm/dd/yyyy#.

However, I have a small tool I use (that I made before I read this) that
passes it as #yyyy/mm/dd# which works fine. This is my PCs location setting,
as well.

So I did a couple of tests, both writing SQL directly into the query builder
and passing SQL statements from VBA using OpenRecordset and variables, and it
seemed to work fine regardless of what date format I chose (US, UK, ISO).
The only time it choked was if I gave it a two-digit year.

I use Access 2007 SP2.

Does anyone know if this is a new thing that was designed into Access 2007?
Or am I just lucky? Or has my PC been taken over by friendly aliens who are
actually doing the hard work for me?
Click to expand...

AFAIK, it has always been that way. The rule is that a date
enclosed in #s must be in an unambiguous style. Dates such
as 01/02/03 are totally ambiguous so Access will try to
guess what you intended by defaulting to US style. A four
digit year can not be a month or day, so using a four digit
year removes part of the ambiguity, but month and day can be
either way, again defaulting to US style, UNLESS one of them
is greater than 12 where it must be a day or illegal.

Because all that is a confusing guessing game, it has long
been recommended to use the style yyyy-mm-dd. Note that it
is possible to have the Windows date format set to use
separators other than slash (/) so Access tries to
accomodate the setting when converting or formatting dates
to text/display. But, the Windows setting may not be
something that Access can deal with so the dash is more
reliable than slash when converting a date to text and back
to a date.
 
Date literals in VBA are not supposed to depend on language settings, because VBA programms should run independently of those settings. Therefore date literals are parsed with the english language settings.

However, date literals can be parsed with different formats. The doc for Office 2010
msdn (dot) microsoft (dot) com/en-us/library/office/gg278460%28v=office.14%29.aspx
is not very specific, it just talks about recognizable dates. The details are a bit gory, see
msdn (dot) microsoft (dot) com/en-us/library/dn528865.aspx

You may find that #2013-12-24# and #24 Dec 2014# are both recognized as date literals. You will probably only get to see the literal #12/24/2013#, because the VBA-Editor silently changes it for you.
 
Back
Top