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

Problem with inserting date in database

100+
P: 132
Hello,

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")
  2.  
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.

Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,370
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

100+
P: 132
I tried your suggestion but no success:
Expand|Select|Wrap|Line Numbers
  1. InvoiceDate = CDate(request.querystring("Idate"))
  2.  
When I read it out of the database I get 00:00:21
Feb 25 '11 #3

jhardman
Expert 2.5K+
P: 3,405
Pass it in a nonambiguous format: February 1, 2011 or 2011-feb-1.

Jared
Feb 25 '11 #4

100+
P: 132
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

jhardman
Expert 2.5K+
P: 3,405
What kind of database is this? Sql server? MySQL? Oracle?
Feb 25 '11 #6

100+
P: 132
It's just an ordinary access database. So an .mdb.
Feb 25 '11 #7

jhardman
Expert 2.5K+
P: 3,405
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?

Jared
Feb 26 '11 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
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

100+
P: 132
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

NeoPa
Expert Mod 15k+
P: 31,541
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.