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)

does not work, 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))


SELECT convert(date,CONVERT(varchar(10),columname,101))
Share This Via: