Convert INT to DATETIME (SQL)
446,059
Solution 1
you need to convert to char first because converting to int adds those days to 1900-01-01
select CONVERT (datetime,convert(char(8),rnwl_efctv_dt ))
here are some examples
select CONVERT (datetime,5)
1900-01-06 00:00:00.000
select CONVERT (datetime,20100101)
blows up, because you can't add 20100101 days to 1900-01-01..you go above the limit
convert to char first
declare @i int
select @i = 20100101
select CONVERT (datetime,convert(char(8),@i))
Solution 2
Try this:
select CONVERT(datetime, convert(varchar(10), 20120103))
Solution 3
use a where
clause on that field to ignore nulls and zero values
update
table
set
BDOS= CONVERT(datetime, convert(char(8), field))
where
isnull(field,0)<>0
Related videos on Youtube
Author by
Daniel
Updated on August 07, 2021Comments
-
Daniel over 2 years
I am trying to convert a date to datetime but am getting errors. The datatype I'm converting from is (float,null) and I'd like to convert it to DATETIME.
The first line of this code works fine, but I get this error on the second line:
Arithmetic overflow error converting expression to data type datetime.
CAST(CAST( rnwl_efctv_dt AS INT) AS char(8)), CAST(CAST( rnwl_efctv_dt AS INT) AS DATETIME),
-
Chris Adragna over 13 yearsWhat language are you using? Can you show us the value(s) of rnwl_efctv_dt?
-
-
Daniel over 13 yearsI'm doing this for hundreds of thousands of rows. Is there anything I need to look out for?
-
Daniel over 13 yearsOr can I just stick this right above my casts (one of which is turning into your first convert statement)?