Date & Time SQL Conversion Error! Please Help!

Question

Date & Time SQL Conversion Error! Please Help!

lparks2001

SSC Veteran

Points: 278
More actions
January 9, 2003 at 3:16 pm

#79614

I have an ASP page that is inserting into our database... When i do the insert i get this error message
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Here is the code.. Can anyone help me? I've been trying to figure this out for days.
case "adduser"
usertemp = "'Temp: " & date() & time() & "'"
sql = "insert into members (username, active) values (" & usertemp & ", 0)"
xba.execute(sql)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

quote:
I have an ASP page that is inserting into our database... When i do the insert i get this error message
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Here is the code.. Can anyone help me? I've been trying to figure this out for days.
case "adduser"
usertemp = "'Temp: " & date() & time() & "'"
sql = "insert into members (username, active) values (" & usertemp & ", 0)"
xba.execute(sql)

Couple of questions.

What datatypes are the fields?

Are there any other fields in the table?

If so, how are they populated?

Also, why do you use seperate Date() and Time() functions? If you need both date and time use the Now() function.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

lparks2001 SSC Veteran Points: 278 More actions · Answer 2

Here are the answers to your questions...

1. The datatypes were datatime, i changed them to varchar, then it worked.

2. Yes there are other fields in the table

3. They are populated with general information like fistname, lastname etc.. All Varchar & Primary Key. Those were the only 2 datetime datatypes.

This is someone's elses code i was trying to correct. I'm not sure why they didn't use the Now() function. I can change that however.

Thanks...

philcart SSC-Forever Points: 47794 More actions · Answer 3

quote:
1. The datatypes were datatime, i changed them to varchar, then it worked.

So the username field was datetime?? If that was the case then the 'Temp: ' portion of the usertemp assignment would have been the first problem to cause problems. The other would have been that there was no space between the date and time.

The statement should have been something like,

usertemp = "'" & date() & " " & time() & "'"

or simply

usertemp = Now()

Also, FWIW I've found that the best way to handle dates is to use the 3 character month abbreviation and a 4 digit year. Eg: '8 Jan 2003'

The 3 character month avoids any possible confusion due to incorrect regional settings. The same date can be passed as '2003 8 Jan' or 'Jan 2003 8'. SQL Server will always get the correct date.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

lparks2001 SSC Veteran Points: 278 More actions · Answer 4

Thanks phil i really appreciate your knowledge. I will make these changes and remember this for the future.

thanks again.