CODEWITHSUNDEEP
sqlsql-serverdatebetween
Natalie
Natalie

Reputation: 3

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?

Upvotes: 0

Views: 400

Answers (2)

Marian
Marian

Reputation: 18

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.

Upvotes: 0

Theo27
Theo27

Reputation: 405

You can use the MONTH() and DAY() functions in MySQL to extract the month and day from the valid_from and valid_to dates, and then check if February 1st falls within the date range:

SELECT 
  valid_from, 
  valid_to, 
  CASE WHEN MONTH(valid_from) < 2 AND MONTH(valid_to) > 2 THEN 'yes' 
       WHEN MONTH(valid_from) = 2 AND DAY(valid_from) <= 1 AND MONTH(valid_to) > 2 THEN 'yes' 
       WHEN MONTH(valid_to) = 2 AND DAY(valid_to) >= 1 AND MONTH(valid_from) < 2 THEN 'yes' 
       WHEN MONTH(valid_to) = 2 AND MONTH(valid_from) = 2 AND DAY(valid_from) <= 1 AND DAY(valid_to) >= 1 THEN 'yes'
       ELSE 'no' 
  END AS keep
FROM your_table;

Upvotes: 0

Related Questions