Arithmetic overflow while conv to datetime 1

[dobe1]

Need a little help on something that is probably elementary.

I have tried the following update:
Update cust_table set cbildt_1 = cast(cbildt as datetime)

cbildt is a decimal 8,0, and the data is a format of i.e.
20050131. There are also some 0 (zero) values.


This is the error that I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

The odd thing is that I have run the following code without
a hitch:
update cust_wo1_table set dtime_date =
cast(woadat as datetime)
where dtime_date is null

In this case, woadat is also a decimal 8,0 with the same
format of i.e. 20050131.

Any help would be appreciated.

 

[vongrunt]

If 20050131 is decimal, it represents number of days since 1900-01-01. Convert it to varchar first:

-- this crashes
select cast(20050131 as datetime)
-- this works
select cast(convert(varchar(8), 20050131) as datetime)

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[JamesLean]

Those values are character representations of a datetime value, not a numeric one (even though it is a numeric field). Therefore you would need to do:

UPDATE tbl
SET dt_col = CAST(num_col AS varchar)

The reason your second query works is that you are only converting NULL values, which would convert as NULL datetimes fine!

--James

 

[dobe1]

Vongrunt,
Why use the convert inside a cast, instead of a cast inside a cast? Is this just style, or does it matter?
I had tried: cast(cast(cbildt as char(8)) as datetime), but
this did not work either.


I tried your code, but it did not work on my data set.
select cast(convert(varchar(8), 20050131) as datetime)

I received this error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

JamesLean,
I had also attempted a cast(cbildt as char(8)), hoping for and implicit conversion, but with no luck.

I tried your code using varchar(8), and received the following error also:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Also James, this code:
'update cust_wo1_table set dtime_date =
cast(woadat as datetime)
where dtime_date is null'
actually references a column, which is not null. Therefore, it is populating a null column with a non-null value.

Thanks,

Dobe

 

[JamesLean]

Sorry, you're right about that second query.

Try adding a WHERE cbildt <> 0 to the end of my query.

--James

 

[vongrunt]

CONVERT() or CAST(), doesn't matter. Both do the same thing. I prefer CONVERT() because of somewhat cleaner syntax and 3rd optional argument.

Reason for syntax error can be invalid "date" value (year less than 1753, month > 12 etc). If problem persists even after you add WHERE clause (c) JamesLean, better check for such values.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[dobe1]

Reason for syntax error can be invalid "date" value (year less than 1753, month > 12 etc). If problem persists even after you add WHERE clause (c) JamesLean, better check for such values. "

That may be the problem. Thanks. I will check, and get back with you.

Dobe

 

[dobe1]

If you look at the 5th line down in my original question, you will see this: 'There are also some 0 (zero) values.'

The following code solved the problem.
Update cust_table set cbildt_1 = cast(cbildt as varchar(8))
where len(cbildt) = 8

Thanks to both of you. I appreciate the help. James, this is a modification on what you suggested. Both of your latest suggestions solved the problem.

Thanks again. This has saved me time.

Dobe