Convert INT to DATETIME (SQL)

Question

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),

Precept

What language are you using? Can you show us the value(s) of rnwl_efctv_dt? – Ignatz

Answer 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))

Stormy

I'm doing this for hundreds of thousands of rows. Is there anything I need to look out for? – Precept

Or can I just stick this right above my casts (one of which is turning into your first convert statement)? – Precept

Answer 2

Try this:

select CONVERT(datetime, convert(varchar(10), 20120103))

Fordo

Answer 3

A simpler, and possibly faster solution is to use DATEFROMPARTS and a bit of arithmetic.

DECLARE @v bigint = 20220623;
SELECT DATEFROMPARTS(@v / 10000, @v / 100 % 100, @v % 100);

Result
2022-06-23

db<>fiddle

Domoniquedomph

I like this way very much ;) – Alaniz

Answer 4

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

Lunchroom

Answer 5

Convert(VARCHAR(10), CAST(CONVERT(char(8), "Replace with you date") as date), 101) "Your alias"

Mouse