Get data for last 4 week and issue \"Arithmetic overflow error for type varchar\"
-
I am trying to get data only for last 4 weeks. My field where I have dates is called [WED], what I normally would do is:
I would declare some variables
DECLARE @CurrentDateTime DATETIME = GETDATE(); DECLARE @CurrentDate DATE = @CurrentDateTime;
DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate)); DECLARE @LWDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);
DECLARE @Last4WeekDt DATE = DATEADD(WW, -3, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt)); DECLARE @Last4WeekDATE VARCHAR(8) = convert(varchar(8),cast (@LAST4WEEKDT as date),112);
and just use WHERE statement
where [WED] BETWEEN @CurrentDate AND @Last4WeekDt
However, I am getting an error namely: "Msg 206, Level 16, State 2, Line 17 Operand type clash: date is incompatible with float"
When I am trying to use this, I am still getting an error
where CONVERT(DATE, CONVERT(CHAR(8),[WED] )) >= DATEADD(WEEK, -4, GETDATE())
Error "Arithmetic overflow error for type varchar, value = 20181006.000000."
Once I used this query and it helped, but this time I am still getting an error. Does someone know where can be a mistake?
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(8), ISNULL(NULLIF([WED], 0),19800101)), 112) < DATEADD(DAY,-30,GETDATE()) -- keeps 0s, older than 30 days
I just need to get 4 weeks of data.
-
DECLARE @EndTargetDate DATE = '01-Jan-2022'
SELECT
FROM
WHERE SomeDate > DATEADD(week,-4,@EndTargetDate) AND SomeDate <= @EndTargetDate
-
Pietlinden thank you. I am getting an error "Operand type clash: date is incompatible with float".
-
why do you need to convert to char(8) ?
where CONVERT(DATE, CONVERT(CHAR(8),[WED] ))
What's the data type of your column [WED] ??
Rule number 1 ( with ANY RDBMS ): tell your system what you know !!!
Rule number 2 ( with ANY RDBMS ): Declare columns with the correct data type !!!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
by the looks of it WED is a float - bad bad choice for almost anything and definitely very bad for a date.
-- as the column you are comparing to is a float (or numeric) you need to convert the date to a numeric representation.
-- convert the date to a char(8), format 112 and then to a float
DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LWDATE float = convert(float, convert(char(8), @LastWeekDt , 112));
DECLARE @Last4WeekDt DATE = DATEADD(WW, -3, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @Last4WeekDATE float = convert(float, convert(char(8), @Last4WeekDt , 112)); -
frederico_fonseca
Thank you very much. It fixed my issue. I appreciate it.
-
Johan Bijnens it appeared to be in float as I understand, not sure why.
-
Thank you for the feedback.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Just to confirm what happened here and according to the other thread on a similar subject, it's because someone designed the table with numeric dates as Jeremy pointed out in the first post in this thread.
@Jeremy... I don't know what is using this table but I strongly recommend that either someone do the necessary conversion and replace the column as an actual DATE datatype column, or and a persisted, indexed, computed column that you can use much more effectively in SQL. Actually, and the trigger police are sure to make an appearance, I'd make a trigger to populate the additional column to prevent any chance of it being interpreted by the system as a scalar function. See the both of the following for why I say that. TGhe first identifies the problem and the second is the ultimate and probably best fix.
https://www.brentozar.com/archive/2020/10/using-triggers-to-replace-scalar-udfs-on-computed-columns/
--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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply