question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked cooldadtx commented

Case Statement using date months

Hi,

I have a date variable 'Sign_Up' formatted as YYYYMMDD

I would like to create a new variable based on this 'Sign_Up' variable and using todays date to essentailly say:

If 'Sign_Up' > 6 months from todays date then 'No' else 'Yes'

Any idea how to best write this please?

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is the data type of @Sign_Up?

0 Votes 0 ·
cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

Putting it inside a CASE statement just requires you to put a boolean expression.

DECLARE @testTable TABLE (Sign_Up DATETIME)
INSERT INTO @testTable VALUES
('1/31/2020'),
('5/31/2020'),
('11/30/2020'),
('1/31/2021'),
('5/31/2021'),
('10/14/2021'),
('10/15/2021'),
('10/16/2021'),
('11/30/2021')

SELECT t.Sign_Up, 
    CASE WHEN ABS(DATEDIFF(m, t.sign_up, GETDATE())) > 6 THEN 'No' Else 'Yes' END
FROM @testTable t


A few notes here. Time comparison is a little sensitive so you need to consider how you handle values near your magic 6 month boundary. In the above code it is just looking for months being greater than 6. Given a date of 2021-04-15, for example, the month has to be greater than 10 (at least Nov) even though 2021-10-15 would technically be 6 months away. But because it is diffing on month it wouldn't flag it until the month becomes 11. You cannot simply change it to >= as then all of 2021-10 would be valid. The alternative is to use days but this doesn't take into account months that have 28/29/31 days.

Another note is that the assumption is the dates you want to check are in a table so I'm using a table variable to allow you to easily test values. However where the source date comes from is completely irrelevant. It could be variable, sproc parameter, etc. Doesn't matter, the case statement is the same.

Finally note I'm using ABS to catch dates on both sides of 6 months. If you don't do that then the diff becomes negative and it won't flag anything after that.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

When you calculate the month number, DATEDIFF(m, date1, date2) is not accurate. See the following sample:

 SELECT CASE WHEN ABS(DATEDIFF(m, '2020-10-31', GETDATE())) > 6 THEN 'No' ELSE 'Yes' END, DATEDIFF(d, '2020-10-31', GETDATE())

There are only 166 days between 2020-10-31 and 2021-04-15. So it is not 6 months.

0 Votes 0 ·

Refer to my notes in my answer. 6 months != 180 days. It is up to the business rules to determine what "6 months" means. Literal 6 months vs 180 days approximation vs actually doing a month/day comparison which is the most accurate but most time consuming.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

Try this:

 -- If the data type of @Sign_Up is varchar
 DECLARE @Sign_Up varchar(8) = '20201001';
 DECLARE @OverSixMonths varchar(3);
    
 IF DATEDIFF(day, @Sign_Up, GETDATE()) > 180
 BEGIN
     SET @OverSixMonths = 'Yes';
 END
 ELSE
 BEGIN
     SET @OverSixMonths = 'No';
 END
 GO
    
 -- If the data type of @Sign_Up is int
 DECLARE @Sign_Up int = '20201001';
 DECLARE @OverSixMonths varchar(3);
 IF DATEDIFF(day, CAST(@Sign_Up AS varchar(8)), GETDATE()) > 180
 BEGIN
     SET @OverSixMonths = 'Yes';
 END
 ELSE
 BEGIN
     SET @OverSixMonths = 'No';
 END
 GO
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.