Is there a less ugly way to select all records that have a date-range which contains (let's say) February 1st than a case-when statement?
My table looks like this, with 'keep' as the desired output:
valid_from | valid_to | keep |
---|---|---|
2013-12-20 | 2014-02-06 | yes |
2014-02-06 | 2014-11-07 | no |
2014-11-07 | 2015-11-19 | yes |
2015-11-19 | 2016-11-19 | yes |
I can fix this using a case-when statement for each year:
case when '2014-02-01' between valid_from and valid_to then 'yes'
when '2015-02-01' between valid_from and valid_to then 'yes'
when '2016-02-01' between valid_from and valid_to then 'yes'
else 'no' end as keep
and so on untill 2023 or somewhere in the future.
Maybe my code will not be used in 2030 so I could expand but there's an itch here.
I tried dayofyear, but that works only if both dates are in the same year.
What do I miss?
I would take the year from valid_from and form my desired date to be checked if it falls between valid_from and valid_to. I would try something like this:
SELECT
DATE_FROM,
DATE_TO,
CASE WHEN CONVERT(DATETIME, CAST(YEAR(DATE_FROM) AS VARCHAR(4)) +'-02-01') BETWEEN DATE_FROM and DATE_TO
THEN 'Yes'
CASE WHEN CONVERT(DATETIME, CAST((YEAR(DATE_FROM)+1) AS VARCHAR(4)) +'-02-01') BETWEEN DATE_FROM and DATE_TO
THEN 'Yes'
ELSE 'No' END AS Keep
FROM Table
Later edit: I also included intervals that start in a year later than 1 Feb and end in another year.