Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been doing a study in dates for some time as I have
a DB that uses them a lot. I would appreciate it if
someone can confirm my findings. My computer settings viz.
Regional Options settings of the Control Panel are English
(U.K.).

Date literals used in queries
1. Calculations of dates & interpretation of dates are
determined by the computer settings.
2. Where dates are written in English(U.S.) format, they
are changed on screen as per the computer settings e.g.
#01/13/2004# to #13/01/2004#.

String dates used in queries
1. Calculations of dates & interpretation of dates are
determined by the computer settings.
2. Where dates are written in English(U.S.) format, they
are NOT changed on screen as per the computer settings but
are calculated as per the computer settings none the less
e.g. DateAdd("ww",1,"01/13/2003") = 20/01/2003.

Date literals used in VBA
Dates should and will be interpretated as English(U.S.)
and calculated as such but will output the date as per the
computer settings e.g. DateAdd("ww", 1, #1/11/2003#) =
18/01/2003 i.e. 18th of Jan

String dates used in VBA
1. Dates will be interpretated as per the computer
settings and outputs as per the computer settings as well.
For example DateAdd("ww", 1, "1/11/2003") = 08/11/2003
i.e. 8th of Nov
2. Dates that could NOT comply according to the computer
settings will be interpretated as English(U.S.) but will
still output the result as per the computer settings e.g.
DateAdd("ww", 1, "1/13/2003") 20/01/2003 i.e. 20th of Jan

Conclusion
In queries use the computer settings to write dates. The
results will also comply to the computer settings.
In VBA dates should be written as English(U.S.) and the
resulting dates will follow the computer settings format.
If the dates are required to be written according to the
computer settings, then use the Format function
to 'persuade' the dates in that way.
 
As far as I'm aware it's simpler than that if you merely want to avoid
problems:

1) Date literals in queries and VBA will always be correctly interpreted
if you do them like this:
#mm/dd/yyyy#
#12/01/2004# 1 December 2004
However
#13/01/2004# 13 January 2004

2) Access always does its best to interpret a string representation of a
data. First it tries the current local settings. If the date isn't valid
according to these it tries to interpret it some other way. Usually this
is welcome:
"1/6/04", "01/06/04", "01/06/2004", "2004-06-01", "1 June 04"
and so on are all interpreted as #06/01/2004#
but sometimes it is confusing
"30 Jun 04" -> #06/30/2004#
"31 Jun 04" -> #04/06/1931# !

3) Dates will always be displayed according to the regional settings
unless you override them, e.g. with the Format() function.
 
The important thing to realize is that dates are actually stored as 8 byte
floating point numbers, where the integer part represents the date as the
number of days relative to 30 Dec, 1899, and the decimal part represents the
time as a fraction of a day. In other words, dates, as stored, do not have a
format. The issue is making sure that Access recognizes your dates
correctly, so that they're stored correctly

Bottom line is that whenever you use # as a delimiter for a date, it should
be in mm/dd/yyyy format, regardless of what the Short Date format has been
set to on the workstation. Something like #18/01/2003# would be correctly
interpretted as 18 Jan, 2003, but only because there's no month 18. If the
first 2 digits are 12 or less, they will always be interpretted as month.

The CDate function does respect the Regional settings. If you were to use
CDate('11/01/2003'), it would be interpretted as 11 Jan, 2003 if the
workstation has the Short Date format set to dd/mm/yyyy. I wouldn't advise
relying on implicit data coercion like in your example of DateAdd("ww", 1,
"1/11/2003") = 08/11/2003, as you can never be certain what your users have
their Short Date format set to.

Take a look at Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html for answers to most of
your questions.
 
John

Queries are a bit more confusing:

* If you look at the SQL View of the Queries, the literal dates are
definitely displayed in the format "#mm/dd/yyyy#".

* If you look at the Query Grid, e.g. criteria row, the display depends on
the Regional Settings. On my PC (Australia & std date display is
dd/mm/yyyy), the literal dates are displayed and entered using
"#dd/mm/yyyy#".

It is rather confusing and because I am so used to #mm/dd/yyyy# in VBA, I
normally switch to SQL View when I have to use literal dates in queries.
 
Thanks for catching that, Van. It must be a long time since I used a
literal date in the query design grid!
 
Back
Top