473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date Format when inserted in Database

132 100+
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
1 4005
jhardman
3,406 Expert 2GB
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

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

Similar topics

6
by: Greg | last post by:
Isn't the following date format supposed to work with any installation of MySQL? YYYY-MM-DD HH:NN:SS It's not working on one system... the date is shown as all zero's.
0
by: John Smith | last post by:
I'm passing two date parameters into a crystal report but it won't let me input anythign other than mm/dd/yy. What I want to put in is dd/mm/yy. Here is my code: Dim cr As New cleaningHistory ...
1
by: Laurence Neville | last post by:
This is regarding a change in the Short Date format under Hebrew Regional Settings, that has caused huge problems in our ASP web application. The change appears to have been introduced sometime...
5
by: jeff | last post by:
i have written a program with date format as m/d/yyyy when i deploy it to client's machine, due to the client use d/m/yyyy format the Select SQL statement return some record wrongly. how can i...
7
by: mewanalwis | last post by:
Dear Friends, I have a rather strange problem which invloves SQL server and ASP. The problem is this. I have an ASP application which use a SQL server. it saves date values with MM/dd/yyyy...
2
by: jpr | last post by:
Friend, I am using a code to export data from a table to a word document using bookmarks and have a problem with a date format. When I export a field that is a mm/dd, in my word doc it shows up...
1
by: somaskarthic | last post by:
Hi In postgresql database how to retrieve the date in a required format ? Is ther any method to retrieve the stored date value in mm/dd/yyyy format. I don't want to set the date format in...
2
by: mrteno | last post by:
I'm using mysql, and used field with DATE type the default date format in my database is yyyy/mm/dd how to change the date format to dd/mm/yyyy
10
by: ARC | last post by:
Hello all, General question for back-end database that has numerous date fields where the database will be used in regions that put the month first, and regions that do not. Should I save a...
7
by: Vishnu R | last post by:
Dear All, I am trying to format the data to DMY in the excel file which is exported to excel using the transfer spreadsheet command. I am trying to format using text to columns as udner. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.