468,483 Members | 2,499 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,483 developers. It's quick & easy.

Problem with inserting date in database

132 100+

I'm trying to insert a date into a database but it doesn't want to go in in the right format.

I want to insert the date 01/02/2011 (dd/mm/yyyy).
I added a response.write to the page to check if the code is processing it properly and when I send it, it's still the right date. However, when it's being saved in the database it displays 30/12/1899 and when you try to change it's value manually in the database itself it shows 0:00:21

I already tried alternative dateformats such as 2011/02/01 (yyyy/mm/dd) or 02/01/2011 (mm/dd/yyyy) but without any success.

As far as my code is concerned the date is being passed in the url.

Expand|Select|Wrap|Line Numbers
  1. InvoiceDate = request.querystring("Idate")
and that same variable is the one who I'm trying to insert.

I'm inserting multiple dates in the database on other pages and those all work. But it's this one giving me problems.

Anybody has any idea what's causing this?
Feb 25 '11 #1

✓ answered by TheSmileyCoder

What i think is happening is that instead of a date 01/02/2011 you are getting 1 divided by 2 divided by 2011.

10 2031
12,513 Expert Mod 8TB
It's being passed as a string. Likely, what you'll have to do is convert the string into a date or qualify it as a date when you insert it into the database.
Feb 25 '11 #2
132 100+
I tried your suggestion but no success:
Expand|Select|Wrap|Line Numbers
  1. InvoiceDate = CDate(request.querystring("Idate"))
When I read it out of the database I get 00:00:21
Feb 25 '11 #3
3,405 Expert 2GB
Pass it in a nonambiguous format: February 1, 2011 or 2011-feb-1.

Feb 25 '11 #4
132 100+
I tried that as well but now it returns 12:01:25 AM

I don't understand why it keeps returning a time, although the properties in my database are Date/Time as datatype and the format is set as Short Date.
Feb 25 '11 #5
3,405 Expert 2GB
What kind of database is this? Sql server? MySQL? Oracle?
Feb 25 '11 #6
132 100+
It's just an ordinary access database. So an .mdb.
Feb 25 '11 #7
3,405 Expert 2GB
Let me get an access expert to weigh in. Meanwhile, what query are you using to pull the data out? And have you applied any regionalization settings to the db?

Feb 26 '11 #8
2,321 Expert Mod 2GB
What i think is happening is that instead of a date 01/02/2011 you are getting 1 divided by 2 divided by 2011.
Feb 26 '11 #9
132 100+
Got it!

It seems it was a stupid syntax-error.
Instead of having '" I was having "' which caused my variable to be treated like integers, like TheSmileyCoder said, and not as a string.

A stupid error but that's what you get when cidingin Notepad :-)

So thank you everybody!
Feb 26 '11 #10
32,093 Expert Mod 16PB
Nice one Smiley.

For full details of using dates in SQL (especially Access Jet SQL) see Literal DateTimes and Their Delimiters (#).
Feb 28 '11 #11

Post your reply

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

Similar topics

3 posts views Thread by Marauderz | last post: by
reply views Thread by Edwin.Madari | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.