Post reply

Error Assitiance

  • March 17, 2016 at 12:01 pm

    #305740

    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

    #1865959

    Try converting the integer values to character strings first, then to datetime.

  • March 17, 2016 at 12:08 pm

    #1865960

    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

    #1865961

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 17, 2016 at 12:16 pm

    #1865963

    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