Arithmetic overflow error converting expression to data type datetime when selec

Ahh, the code was getting sunday not saturday, try this.

EDIT: one correction

CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT )) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

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.

Ahh, the code was getting sunday not saturday, try this.

EDIT: one correction

CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT )) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

Try it like this, casting both sides as a date to strip any time from each.
https://stackoverflow.com/questions/12422248/previous-monday-previous-sundays-date-based-on-todays-d… Opens a new window
https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-da… Opens a new window

AND cast(p.[PDWEDT] as date) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1+1, GETDATE()) as date)

jrp78

Thank you very much for your reply. For some reason I am getting an error when I use the code from your message. I also tried a couple of things from the Stack overflow that you have provided, still getting an error for some reason…

jrp78

It is numeric.

What are some of the typical values stored in that numeric field which are supposed to be dates?

I have seen many cases where numeric values such as ‘20210725’ are stored as numbers and are intended to be read as date values (in this case 2021-07-25 or 2021 July 25).

If your numeric value looks like a YYYYMMDD in numeric form, try using:

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), 20210725 ))

Replace 20210725 with your numeric field PDWEDT .

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), PDWEDT ))

Alec6638

Thank you very much for your response.

If I just run the code with the date like ’11-27-2021′ I am getting results. p.PDWEDT is ‘Week Ending Date’ in the table.

But if I write with the code from jrp78 / stack overflow forum, I am not getting an error but I don’t receive any results.

Now that you have converted the field, don’t cast the right hand side. Try this

CONVERT(DATETIME, CONVERT(CHAR(8), p.PDWEDT )) = DATEADD(d, DATEPART(DW,GETDATE())*-1+1, GETDATE())

Now that you have converted the field, don’t cast the right hand side. Try this

SQLCONVERT(DATETIME, CONVERT(CHAR(8), p.PDWEDT )) = DATEADD(d, DATEPART(DW,GETDATE())*-1+1, GETDATE())

Since he is only looking at dates, and not datetimes, he may have to use the following (DATETIME changed to DATE), and keep the date cast on the right side.:

AND CONVERT(DATE, CONVERT(CHAR(8), PDWEDT )) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1+1, GETDATE()) as date)

jrp78

Thank you for your reply. For some reason, I am still not getting any results.

Alec6638

Thank you for the reply.

Still same issue.

Please try:

AND CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT )) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1+1, GETDATE()) as date)

Ahh, the code was getting sunday not saturday, try this.

EDIT: one correction

CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT )) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

jrp78

Oh God, thank you!!

It finally worked. I really appreciate your willingness to help. Thank you.

Alec6638

I also appreciate your help. Thank you very much.

jrp78

Oh God, thank you!!

It finally worked. I really appreciate your willingness to help. Thank you.

Alec6638

I also appreciate your help. Thank you very much.

Excellent!

Please give ‘Best Answer’ to jrp78

Your daily dose of tech news, in brief. Welcome to the Snap! Flashback: September 1, 1994: US Library of Congress Starts Virtual Library Project (Read more HERE.) Bonus Flashback: September 1, 1977: First Flyby of Saturn (Read more HERE.)…

Welcome to another SpiceQuest! In this series, we call out current holidays and give you the chance to earn the monthly SpiceQuest badge! (Each task can be done at any time. They don’t have to be completed on a certain holiday.) This month…

Hello everyone,Im trying to install Asus display driver for a user via Intune but there is no silent install switch for this software. I tried /q /s and so on but it doesnt help. Does anyone got an idea how to deploy this software?

Any suggestions? Sophos support says it’s my DNS server.If it’s my DNS server, why does DNS work fine for iOS devices on site connected to wifi?If it’s my DNS server, why does DNS work fine for Windows workstations through the vpn?Does anyone have this w…

OK, so I have to ask, “who stole summer and what did you do with it”? With my son heading back to school and all the staff back from holidays the long grind from September to December starts. Summer seemed to last longer when I was younger. As things …

You are watching: Arithmetic overflow error converting expression to data type datetime when selec. Info created by THVinhTuy selection and synthesis along with other related topics.

Rate this post

Related Posts