How to handle dates - UK format

[WIS]

There seems to be a lot of questions on dates using the UK format. I use the following to maintain consistent results.

Whilst the American format for dates is Month/Day/Year there are a lot of countries, eg Great Britain, that use the format of Day/Month/Year. This brings up some inconsistencies for users of the UK format.

The following is addressing dates in the UK format ie Day/Month/Year.

In general dates entered into tables and queries act as expected, but dates in SQL and VBA can act inconsistently. For example, in a query, 1/5/09 responds as 1 May 2009 but the same entry in SQL or VBA shows up as 5 Jan 2009.

Things to do:

The first thing to do is to make sure that the date settings in Windows is dd/mm/yyyy or similar format. A standard Windows installation seems to leave the date in the mm/dd/yyyy format.

In all tables and forms enter dd/mm/yyyy or similar in the Format section of table fields and date controls. This seems to be an undocumented feature but works 100% of the time.

With the above done, entering #1/5/09# or 1/5/09 (form or other reference) via the Criteria cell will always show as 1 May 2009.

SQL and VBA:
If a date is entered as 1/5/09 it shows as 5 Jan 2009. A strange anomaly is if the 1st number is 13 or more then it will show correctly. eg 13/5/09 returns 13 May 2009, but 12/5/09 returns 5 Dec 2009.

Workaround:

This workaround seems to be 100% consistent. Use the Format function Format(MyDate,"dd/mmm/yyyy"). What is interesting is that if you only put in 2 m's it seems to revert back to the American format - you must always use 3 m's.

VBA example:
Dim myDate
MyDate = Me.cboSomething(That Contains a Date)
Me.RecordsetClone.FindFirst "[DateField] = #" & Format(myDate, "dd-mmm-yyyy") & "#"

Use a similar approach for SQL.

 

[HiTechCoach]

VBA assumes automatically that any dates being used follow US Settings, ie #mm/dd/yyyy#. Use the Format function to return the correct date.

'Code Courtesy of
'Joe Foster
'
Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
Global Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"

and then

SQL = SQL & "When >= " & Format$(Now, JetDateTimeFmt)

From:
http://www.mvps.org/access/datetime/date0005.htm

 

[Rich]

I've never had any problem with just
stLinkCriteria = "[TrDate]>=" & "#" & Format(Me![Datetxt], "mm/dd/yyyy") & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[LPurvis]

FWIW I never, ever venture away from international (ISO) format.
That way nobody "wins" ;-) And there's absolutely zero ambiguity of it when read.

Format(myDate, "\#yyyy\-mm\-dd\#")
i.e. in use
Me.RecordsetClone.FindFirst "[DateField] = " & Format(myDate, "\#yyyy\-mm\-dd\#")

If you have a function which returns this for you (e.g. fFormatDateSQL) then including the delimitation in tthe formatting means it's trivial for a one-stop change to move to a different RDBMS syntax - e.g.
Format(myDate, "\'yyyy\-mm\-dd\'")

Me.RecordsetClone.FindFirst "[DateField] = " & fFormatDateSQL(myDate)

Cheers.