sqlsql-serverdatebetween

Select all records with february 1st between valid_from and valid_to, multiple years - SQL


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?


Solution

  • 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.