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