Skip to content
Advertisement

Before/After Difference for a flexible Date (Case)

i have a little problem with a case. I output two date fields (yyyy-mm-dd). the date changes and is flexible. not every entry has the same date fields.

I take one date field as an fixed point. If the Date is 4 months before the date, a ‘V’ should be the output. if it is behind it, an ‘N’ should be the output.

SELECT
a.FIELD1,
b.FIELD2,
c.FIELD3,
d.DATE1, -- YYYY-MM-DD
e.DATE2 -- YYYY-MM-DD
CASE
    WHEN e.DATE2 >= d.DATE1 THEN = 'N'
    WHEN e.DATE2 < d.DATE1 THEN = 'V' -- >= 4 MONTHS BEFORE
END AS DATE_SIGN

I heard that are problems with the date format i get. The Datediff works only with another format, right?

Can I use the two Dates in a Case like this?

Kind regards

Advertisement

Answer

Stephan, I’m not sure if I understand this correctly but I think you are asking how you can check if DATE2 is more than 4 months earlier than DATE1?

If so you could use the DATEADD function in SQL Server, it would be something like

WHEN DATE2 < DATEADD(MONTH, -4, DATE1) THEN ‘V’

In MySQL the DATE_ADD function is similar but would be

WHEN DATE2 < DATE_ADD(DATE1, INTERVAL -4 MONTH) THEN ‘V’

Advertisement