Date Format when inserted in Database

[Cainnech]

Hello,

I have a problem when I try to insert dates in my database.

I want to insert a date range in my table in the dd/mm/yyyy format.

When I insert for instance 01/05/2011 (1st of May) it shows in my db as 5/01/2011

However when I get to 13/05/2011 it is showing ok.

The result that I get is:

05/01/2011
05/02/2011
05/03/2011
05/04/2011
05/05/2011
05/06/2011
05/07/2011
05/08/2011
05/09/2011
05/10/2011
05/11/2011
05/12/2011
13/05/2011
14/05/2011
15/05/2011
16/05/2011
17/05/2011
18/05/2011
19/05/2011
20/05/2011

So starting at 13/05 that's the right format. Before it's the wrong format.
It has to be something with the date format but I don't know what.

Below is the code I use to add the dates:

<%@ LANGUAGE="VBSCRIPT"%>
 
<% 
 
dim StartDate
dim StopDate
dim CurrentDate
dim SQL
 
StartDate = cdate(request.querystring("From"))
StopDate = cdate(request.querystring("To"))
 
 
response.write("Please wait while we create your page.<br>")
 
 
Set OBJdbconnection = Server.CreateObject("ADODB.Connection")
 
OBJdbConnection.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../data/Meetup.mdb")
 
CurrentDate = cdate(StartDate)
currentdate.formatdate()
Do while CurrentDate <= StopDate
  SQL = "INSERT INTO Meeting (Date) VALUES (#" & CurrentDate & "#)"
  OBJdbConnection.Execute(SQL) 
  response.write(CurrentDate & "<br>")
 
  CurrentDate = DateAdd("d",1,CurrentDate)
 
loop
 
OBJdbConnection.Close 
set SQL = nothing 
 
Response.write("Your page has been created.")
'Response.redirect("db.asp")
%> 
 
 

Anybody that can help me with this?

Thanks,
Kenneth

[jhardman]

Kenneth,

The problem is the regionalization settings for your database. All Microsoft products by default use American regionalization , and you are using MS Access as your database. In the US we do not say "1st May" we say "May 1st", so the default format in the US is mm/dd/yyyy. Up to day twelve you have ambiguous dates; 3/5/2011 and 5/3/2011 could mean the same thing depending on who writes it. The database is guessing which one you mean based on US regionalization .

The best solution to your problem is to use a non-ambiguous date format such as "3-May-2011" or "2011 May 03" or "May 03, 2011" any of which will be interpreted correctly by the database.

Alternatively you could change the regionalization of your database, I'm sure it is possible, but I have never done it in access. But really, the other solution is better from a standards point of view: 3/5/2011 means different things to different people but 3-May-2011 can only mean one thing.

Jared