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"
dteDate = FormatDate(dteDate, "%Y/%m/%d")
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
|
