CODEWITHSUNDEEP
sql-serverstored-procedures
Nisar
Nisar

Reputation: 6038

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

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.

Upvotes: 13

Views: 149045

Answers (4)

mohammadAli
mohammadAli

Reputation: 445

Now we can do simply:

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;
select cast(str(@yr_mnth_dt) as datetime); // output: 2013-08-22 00:00:00.000

Upvotes: 0

msi77
msi77

Reputation: 1632

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

Upvotes: 2

Ian Preston
Ian Preston

Reputation: 39566

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.

Upvotes: 22

Jon Skeet
Jon Skeet

Reputation: 1500805

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

Upvotes: 3

Related Questions