Convert INT to DATETIME (SQL)

user_264425 0
Viewed 475498

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),
integer datetime casting sql
5 Answers

Try this:

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

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
user_166097240

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

user_148689970

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

user_138580270
Related