April 19, 2022 at 6:45 pm
I want to use a case statement in a where clause and I want to use getdate() in conjunction with a date field in my table (MyAuditDate) to determine which where clause I use.
In other words, if getdate() falls between the first day of the month and the Fifteenth day of the month, I want to a WHERE Clause like so:
WHERE MyAuditDate BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0), AND CONVERT(DATE,CONVERT(VARCHAR(6),GETDATE(),112)+'15',112)
If getdate() falls between the Sixteenth and the Last Day of the Month, I want to use different criteria:
WHERE MyAuditDate BETWEEN CONVERT(DATE,CONVERT(VARCHAR(6),GETDATE(),112)+'15',112)
AND DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
I'm guessing I have to use some kind of a CASE Statement to do this, but I'm just not exactly sure how to go about it.
Thanks in advance for any help you can give.
April 19, 2022 at 6:57 pm
Use variables to determine the logic for the comparison values -- e.g.,
DECLARE @dayOfMonth int = DATEPART(DAY,GETDATE())
DECLARE @minDate DATETIME = CASE WHEN @dayOfMonth < 16 THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) ELSE CONVERT(DATE,CONVERT(VARCHAR(6),GETDATE(),112)+'15',112) END;
DECLARE @maxDate DATETIME = CASE WHEN @dayOfMonth < 16 THEN CONVERT(DATE,CONVERT(VARCHAR(6),GETDATE(),112)+'15',112) ELSE DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) END;
-- ...
WHERE MyAuditDate BETWEEN @minDate AND @maxDate
What's the data type of MyAuditDate? To avoid ambiguity, particularly with datetime values precise to only 3 milliseconds, it's recommended to use >= minDate and < maxDate (not inclusive of maxDate) -- e.g.,
= '2022-01-01 AND < '2023-01-01'
rather than
BETWEEN '2022-01-01' AND '2022-12-31 23:59:59.997' -- which could exclude some values if datatype was more precise -- or '2023-01-01 00:00' , which could cause a value at exactly midnight to be included in two separate sets
April 19, 2022 at 7:19 pm
Thanks for the quick response. MyAuditDate is a datetime.
I understand your logic, makes perfect sense. I tried your syntax above, but it doesn't like the "BETWEEN" (boldfaced below), it says incorrect syntax.
DECLARE @minDate DATETIME = CASE WHEN @dayOfMonth < 16 THEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) ELSE BETWEEN CONVERT(DATE,CONVERT(VARCHAR(6),GETDATE(),112)+'15',112) END;
Let me know your thoughts. Thanks again!
April 19, 2022 at 7:48 pm
I think I figured it out, I just took the "BETWEEN" out and that seems to have resolved the issue. Thanks for your help; much appreciated!
April 19, 2022 at 8:20 pm
There is a problem here that needs to be addressed. The code you have for the end of the month will return the last day of the month - at midnight. That will exclude every row on the last day of the month except those that occurred exactly at midnight.
Further to that issue is the fact that you will never get the full results for any period. In order for you get all results for the last day of any period - you need to run this code at the last possible time for that period. That means you would need to run it 3 milliseconds before the end of the period to make sure you included all rows for that period.
I am not sure what your requirements are for this - but I would definitely review those requirements to make sure you are selecting the correct set of data and that you are able to select all relevant data. As written, you will be missing data - and anyone running similar code that does handle that last day of the period is going to get a different answer.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply