July 27, 2022 at 12:59 am
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.
July 27, 2022 at 1:04 am
DECLARE @EndTargetDate DATE = '01-Jan-2022'
SELECT
FROM
WHERE SomeDate > DATEADD(week,-4,@EndTargetDate) AND SomeDate <= @EndTargetDate
July 27, 2022 at 2:21 am
Pietlinden thank you. I am getting an error "Operand type clash: date is incompatible with float".
July 27, 2022 at 6:31 am
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
July 27, 2022 at 8:17 am
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));
July 27, 2022 at 2:52 pm
frederico_fonseca
Thank you very much. It fixed my issue. I appreciate it.
July 27, 2022 at 2:54 pm
Johan Bijnens it appeared to be in float as I understand, not sure why.
July 28, 2022 at 10:37 am
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
July 29, 2022 at 12:22 am
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-2... 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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy