Arithmetic overflow error converting expression to data type datetime when selec

Question

Arithmetic overflow error converting expression to data type datetime when selec

JeremyU

SSChasing Mays

Points: 640
More actions
November 30, 2021 at 3:47 am

#3958525
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.

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

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

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

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

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 2

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

shasivashok SSC Journeyman Points: 79 More actions · Answer 3

Please check the below link for the solution.

https://stackoverflow.com/questions/18416623/arithmetic-overflow-error-converting-expression-to-data-type-datetime-while-di

Phil Parkin SSC Guru Points: 246961 More actions · Answer 4

shasivashok wrote:

Please check the below link for the solution.
https://stackoverflow.com/questions/18416623/arithmetic-overflow-error-converting-expression-to-data-type-datetime-while-di

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

homebrew01

SSC Guru

Points: 55548

November 30, 2021 at 3:26 pm

#3958700

What is the value of PDWEDT ?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

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)