March 17, 2016 at 12:01 pm
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
chrcdt is a int field with Null and no constraints
Table data sample
chgpno chinfr chinto chrcdt chprdt
NPS 20120530 20120530 20120530 20120530
on the following:
;with cte as (select chppon, datediff(day, cast(cast(chrcdt as datetime) as date), cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr "nolock"
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
From cte "nolock"
Group By chppon
March 17, 2016 at 12:07 pm
Try converting the integer values to character strings first, then to datetime.
March 17, 2016 at 12:08 pm
Lynn Pettis (3/17/2016)
Try converting the integer values to character strings first, then to datetime.
Can you provide an example as I am not used to this version of SQL?
March 17, 2016 at 12:10 pm
i think your issue is you are storing integer representations of dates; not sure if you have control, but i would change the datatypes to be [date] or [datetime] data types if they hold dates.
the error appears to this conversion, as an example:
select cast(cast(20120530 as datetime) as date)
select cast(20120530 as datetime)
you could use a calendar table that has the proper date, as well as that same kind of integer date and join to it;
you could convert it inline by changing the inner convert to varchar
select cast(cast(20120530 as varchar) as date) --2012-05-30
Lowell
March 17, 2016 at 12:16 pm
unfortunately it is built into the software that way. I cant alter the production database column types. I can create temp tables.
Lowell (3/17/2016)
i think your issue is you are storing integer representations of dates; not sure if you have control, but i would change the datatypes to be [date] or [datetime] data types if they hold dates.the error appears to this conversion, as an example:
select cast(cast(20120530 as datetime) as date)
select cast(20120530 as datetime)
you could use a calendar table that has the proper date, as well as that same kind of integer date and join to it;
you could convert it inline by changing the inner convert to varchar
select cast(cast(20120530 as varchar) as date) --2012-05-30
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy