Webmaster Forum / ASP / Database Access / July 2008

Access *ONLY* understands USA dates.  MM/DD/YYYY

Sorry, but that's a fact of life.

Okay, it's also not quite true.

If you give it a date such as
    23/6/2008
then it says "Oh, 23 is too large to be a month number, so it must be a day
numbers, so I'll change and assume DD/MM/YYYY format."

But if you give it
   7/6/2008
it will *ALWAYS* assume you mean July 6, 2008.  USA form.

Unlike SQL Server, there is no way to tell Access what format you are using
*AND* the Locale you are using has no impact on the Access SQL query.  I
know.  Ugly.

HOWEVER...

However, there *IS* a universal solution:  Give Access the date in
YYYY/MM/DD format!!  Just as it changes to DD/MM/YYYY when the first number
is greater than 12, it *also* changes to YYYY/MM/DD format when the first
number is greater then 31!!!

In VBScript/ASP this is easy:

<%
Function ISODate( dt )
   Dim temp : temp = "NULL"
   If IsDate(dt) Then
       dt = CDate(dt)
       temp = "#" & Year(dt) & "/" & Month(dt) & "/" & Day(dt)
       If DateValue(dt) <> dt Then temp = temp & " " & TimeValue(dt)
       ISODate = temp & "#"
   End If
End Function
%>

Now, in constructing your SQL queries, you can do things like
    SQL = "SELECT * FROM table WHERE someDate > " & ISODate(xyz)
or
    SQL = "INSERT INTO table (someDate) VALUES(" & ISODate(fooraw) & ")"
Notice how the function puts the #...# in place for you.