MSSQL Error

Craig321

Craig321

Craig321

Soldato
Joined
2 May 2004
Posts
19,950
Hi,

I've been asked to set up a booking system.

The guy that programmed this left after it was finished, and he coded it very specifically. It'll only work under framework/ASP 1.1.

I've got most of it running properly using ASP 1.1 under Windows Server 2003 using MSSQL 2008 but on one of the pages I get this:

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I have tried with a completely blank database yet I still get this error.

The booking system is still running on the creators webserver, but it needs to be moved which is why I'm setting it up, I have an exact copy of the database and the files directly from the working server so I really don't see why it's not working.

I have tried running the database from MSSQL 2000 and I get the same error.

Any ideas? Are there any settings I could change on the server to try fix this?

Thanks,
Craig.
 
It sounds like you're trying to enter an invalid string in to a date field of your database.

Can you show us the query you're running which is generating this error?

It sounds like you need to put a CAST(x as datetime) in there somewhere.
 
Spunkey said:
It sounds like you're trying to enter an invalid string in to a date field of your database.

Can you show us the query you're running which is generating this error?

It sounds like you need to put a CAST(x as datetime) in there somewhere.
Click to expand...

I've tried this with no data in the database.

Thanks,
Craig.
 
Last edited:
Just found the problem:

If I change the datetime column types to varchar then it works fine, however the wrong data is output for the page when they're converted, so I'm guessing the system uses datetime somewhere to order it or something. The second I change any of the date columns to datetime it breaks.

It still doesn't make sense though as the booking system is running perfectly with all default settings on the original server.
 
Last edited:
It could be due to the locale of the different server being set to accept dates in different formats - the date 23/10/2008 would throw the error in the OP if the server is in american format only.
 
Spunkey said:
It could be due to the locale of the different server being set to accept dates in different formats - the date 23/10/2008 would throw the error in the OP if the server is in american format only.
Click to expand...

Your first answer was correct, thanks very much.

Not sure how I missed it all this time, but a load of date entries are similar to:

2006-06-29 12:38:11.997000000

Isn't there correct format supposed to be 2006-06-29 12:38:11 ?

Is there any query to fix them, there's thousands like that :(
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom