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

06odsfpq 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(128)

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?

sql-server

来源：https://stackoverflow.com/questions/75595033/select-all-records-with-february-1st-between-valid-from-and-valid-to-multiple-y

2条答案

按热度按时间

inb24sb21#

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.

赞(0）回复(0）举报 2023-03-07

j5fpnvbx2#

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;

赞(0）回复(0）举报 2023-03-07

我来回答

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

2条答案

相关问题

热门标签

最新问答