By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,868 Members | 1,974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,868 IT Pros & Developers. It's quick & easy.

Date Format when inserted in Database

100+
P: 132
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:
Expand|Select|Wrap|Line Numbers
  1. <%@ LANGUAGE="VBSCRIPT"%>
  2.  
  3. <% 
  4.  
  5. dim StartDate
  6. dim StopDate
  7. dim CurrentDate
  8. dim SQL
  9.  
  10. StartDate = cdate(request.querystring("From"))
  11. StopDate = cdate(request.querystring("To"))
  12.  
  13.  
  14. response.write("Please wait while we create your page.<br>")
  15.  
  16.  
  17. Set OBJdbconnection = Server.CreateObject("ADODB.Connection")
  18.  
  19. OBJdbConnection.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../data/Meetup.mdb")
  20.  
  21. CurrentDate = cdate(StartDate)
  22. currentdate.formatdate()
  23. Do while CurrentDate <= StopDate
  24.   SQL = "INSERT INTO Meeting (Date) VALUES (#" & CurrentDate & "#)"
  25.   OBJdbConnection.Execute(SQL) 
  26.   response.write(CurrentDate & "<br>")
  27.  
  28.   CurrentDate = DateAdd("d",1,CurrentDate)
  29.  
  30. loop
  31.  
  32. OBJdbConnection.Close 
  33. set SQL = nothing 
  34.  
  35. Response.write("Your page has been created.")
  36. 'Response.redirect("db.asp")
  37. %> 
  38.  
  39.  
Anybody that can help me with this?

Thanks,
Kenneth
May 9 '11 #1
Share this Question
Share on Google+
1 Reply


jhardman
Expert 2.5K+
P: 3,405
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
May 9 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.