adOpenStatic Logo
Navigation
Home
FAQ
Ken's Blog
Resources
Contact Ken
Copyright 2000 -



Handling Dates when using Access and ADO
One of the common problems that I see often when using ASP/ADO and Access occurs in non-US locales when dealing with dates. Users attempting to insert non-US-dates into Access find their dates have been "mangled" (in the sense that Access has converted their dates to US format), and this has given rise to a common misapprehension that Access only stores "US" dates.

Access, like all other modern RDBMS systems that I know of, stores dates and times as numbers. For example, the date might be stored as the number of days after or before some arbitrary starting date, and the time is stored as a fraction of a day (eg 0.5 would be midday). The way that Access inteprets a date in an SQL string and/or presents this date to you when you open a table using the Access GUI is a completely separate matter.

So, what is going wrong?
The problem with ADO & Access is that when you pass a date in an SQL string, Access seems to assume that it is in US date format. Eg if you pass 5/10/2001 (meaning 5th, Oct 2001), Access assumes you mean 10th May 2001. Only when the date can not be construed in US mm/dd/yyyy format are other formats (eg UK format) tried.

Fixing the problem
The easiest way to fix this problem is to format your dates in ISO yyyy/mm/dd format, since there is no way that your dates can be (mis)construed as being in US mm/dd/yyyy format. This is easily accomplished using a date formatting routine such as this one.

<%
dteDate = "5/10/2001" ' 5th October, 2001
dteDate = FormatDate(dteDate, "%Y/%m/%d") ' dteDate = "2001/10/05"

strSQL = _
   "INSERT INTO table1 (field1) VALUES (#" & dteDate & "#)"

objConn.Execute strSQL,,adCmdText+adExecuteNoRecords
%>

A final note
In order for date formatting functions to work properly, you need to ensure that ASP also thinks of dates in the same format that you are. In order to set this properly, you can either set the Session.LCID at the top of every page, or if you control your own server, alter the server's settings (click here for instructions).

Back to FAQ Listing | Homepage