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.
-
I've normally seen convert expressions like that dealing with strings rather than numbers. Do you have to start with a numeric type?Jon Skeet– Jon Skeet2013-08-24 08:56:28 +00:00Commented Aug 24, 2013 at 8:56
Add a comment
|
4 Answers
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);
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));
Comments
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
2 Comments
Ian Preston
This is a good approach, but worth noting it's SQL Server 2012 and above only.
Jon Skeet
@IanPreston: Hadn't noticed that. Surely there's some way of creating a date from its constituent parts prior to that without using a string...
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
2 Comments
Ian Preston
According the comments from the OP he has to start with a
numeric type.
msi77
@Ian Preston, but I see that it is august 22, 2013, not the integer. :-)
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
