sql server - Arithmetic overflow error converting expression to data type datetime. (while displaying date time..)

Question

While executing following error is showing

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;
select convert(datetime,@yr_mnth_dt,112) as YR_MNTH_DT

error shows

Arithmetic overflow error converting expression to data type datetime.

Answer 1

You issue is that you're trying to convert the numeric to a datetime, and this just isn't working.

You need to turn your numeric into a string first:

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;

select yr_mnth_dt = cast(cast(@yr_mnth_dt as char(8)) as datetime);

SQL Fiddle with demo.

When you try and convert a numeric type to a datetime, SQL Server tries to add the numeric value as the number of days to the date 01-Jan-1900. In your case this is trying to add millions of days, and hence the overflow error.

CONVERT works fine, too, if you prefer:

select yr_mnth_dt = convert(datetime, convert(char(8), @yr_mnth_dt));

SQL Fiddle with demo.