Case Statement using date months

Question

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?

Answer 1

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.

Answer 2

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