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.

Answer 2

I've only seen the conversion used for strings. I can't easily tell whether it's even designed to work with numbers. You could convert the number to a string, then the string to a date. However, I would personally just use DATEFROMPARTS:

SELECT DATEFROMPARTS(@yr_mnth_dt / 10000, 
                     (@yr_mnth_dt / 100) % 100,
                     @yr_mnth_dt % 100) AS YR_MNTH_DT

Answer 3

Why numeric? Try this

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