Arithmetic overflow error converting expression to data type datetime when selec

  • I have such an issue. I want to select a column PDWEDT (it is numeric) and I need to select previous Saturday. The way how I usually do is with the help of declare statement

     

    DECLARE @CurrentWeekday INT = DATEPART(WEEKDAY, GETDATE())

    DECLARE @LastSunday DATETIME = DATEADD(day, -1 * (( @CurrentWeekday % 7) - 1), GETDATE());

    Afterwards, I have some code and later I just try to select the dates

    AND  p.[PDWEDT] = @LastSunday

    Not sure why but I am getting an error: "Arithmetic overflow error converting expression to data type datetime."

    So I see 2 potential ways to solve this problem.

    1.Either to find another way to select previous Saturday instead of DECLARE statements (though as I understand it is the most common way). or 2. To cast or convert the field, however I am not sure how it can be done since it is numeric at the very moment. I will appreciate any ideas. Thank you. p/s I use SQL Server Management Studio.

  • When you say the PDWEDT column is numeric, how exactly is it stored?  Your code returns '2021-11-28 hh:mm:ss.nnn' as  last Sunday with the precise time dependent on when the code was run.  If the PDWEDT column stores dates as 20211128 etc. then you'll get that error.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Why are you storing date as numeric data type?  You should store it as what it is and compare it to the same data type instead of a numeric data type.  When you store datetime as a numeric data type, SQL Servert will store the number of days that passed since January 1 1900 until the date that you tried to store.  Most chances are that  PDWEDT  has big numbers and when SQL Server tries to calculate the date according to the data that is stored there, it gets a date that passes December 31, 9999 and it gets an overflow error

    Adi

  • Hmmm, calling that a solution is rather generous. It may make things work in your case, but storing a datetime in a numeric column is a weird way of doing things. Counter-intuitive to work with and you lose all baked-in date validation which comes with using the correct datatype for the job.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • What is the value of PDWEDT ?

  • There are several issues here and the most important of them all is an absolute show stopper for us and almost everyone that has posted has mentioned it in one form or another...

    WE CAN'T ACTUALLY SOLVE YOUR PROBLEM UNTIL WE KNOW WHAT THE DATATYPE FOR THE PDWEDT COLUMN IS AND YOU PROVIDE AN EXAMPLE DATE FROM THAT COLUMN!

    So.... any feedback on this so we can actually help or should this problem just continue to be unresolved? 😉

    Your original post is also a bit confusing... You first say...

    JeremyU wrote:

    I have such an issue. I want to select a column PDWEDT (it is numeric) and I need to select previous Saturday.

    ... but then you display the following code...

    Afterwards, I have some code and later I just try to select the dates

    AND p.[PDWEDT] = @LastSunday

    So, which is it??? Are you trying to match for the previous Saturday or the previous Sunday???

    And, while I have your attention, if GETDATE() occurs on the day you want (again, is that Saturday or Sunday), would the "previous" date you're looking for be the date the same as GETDATE() or 7 days prior.

    Heh... enquiring mind want to know. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply