Conversion Error

Question

question

nonipaus asked Mar 12, '13

Conversion Error

Hi, We have just moved our application from SQL Server 2000 to 2005. The following code used to work without a problem, but now I am getting the following error, Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. update COMMODITY set comm_qty = 189300, comm_um = 'LBR', load_date = '2013-03-11 00:00.00.00', user_id = 'kmpeters', proc_date_time = '2013-03-12 11:29.23.831', calc_weight = 189300, load_date_time = '2013-03-11 20:50.01.437' where car_init = 'ALAX' and car_no = '062545' and arrv_date_time = '2013-03-05 15:24.06.000' and compart_no = 1 I would appreciate any help. Thanks

datetime conversion

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Mar 12, 2013 at 06:12 PM

Can you post the DDL of the Commodity table?

0 ·

JohnM commented · Mar 12, 2013 at 06:17 PM

Also, in the conversion, did any of the data types change to say a smalldatetime?

0 ·

Answer 1 · 2013-03-12T18:20:41Z

KenJ answered Mar 12, '13

The minutes should be preceded by a colon rather than a dot. the dot is for the milliseconds only. load_date = '2013-03-11 00:00:00.00', Perhaps SQL Server 2000 was less strict on this.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Mar 12, 2013 at 06:21 PM

Ah, you beat me to it. By all of 20 seconds.

0 ·

JohnM commented · Mar 12, 2013 at 07:00 PM

Nice catch @KenJ! I completely missed it. ;-)

0 ·

Answer 2 · 2013-03-12T18:21:02Z

ThomasRushton answered Mar 12, '13

I think it's to do with the use of '.' instead of ':' to separate minutes from seconds. I could, of course, be wrong. What is your server's default dateformat? And does your client code set a custom format before you start? What was the original server's dateformat? See http://msdn.microsoft.com/en-gb/library/ms189491(v=sql.90).aspx for more.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nonipaus commented · Mar 12, 2013 at 06:43 PM

I am waiting for an answer from my DBA on the dateformat. If I change the code to remove the milliseconds, the query works fine. Thanks

0 ·

KenJ commented · Mar 12, 2013 at 06:50 PM

It probably only works mostly fine. It just assumes 00 for the seconds then treats your seconds as milliseconds

0 ·

nonipaus commented · Mar 12, 2013 at 06:55 PM

This query is send through a Powerbuilder application. We use the datetime datatype to store the variables. It worked fine before and now it fails.

0 ·

question