sqlsql-server

How to check if day of given date is between 2 dates


I am trying to check if today is anniversary day of bookdate given startdate and enddate in SQL Server

I have the ff. query, but it fails if the dates are month end because the enddate starts with the start of next month.

DECLARE @bookdate DATE = '2-28-2023'
,@startdate DATE = '2-28-2023'
,@enddate DATE = '3-1-2023'

SELECT CASE WHEN DAY(@bookdate) BETWEEN DAY(@startdate) AND DAY(@enddate) 
THEN 'Yes' ELSE 'No' END AS isAnniversary;

Solution

  • Change the bookdate to year and month of the startdate

    DATEFROMPARTS(year(@startdate), month(@startdate), day(@bookdate))
    

    and then compare it with start/end date

    bookdate BETWEEN startdate AND enddate
    

    Sample

    DECLARE @bookdate DATE = '20230228';
    
    with date_range as
    (    
      select *
      from
      (
        values
        ('20230228', '20230301'),
        ('20230328', '20230329'),
        ('20230401', '20230402')
       ) d (startdate, enddate)
    )
    select *,
           CASE WHEN bookdate BETWEEN startdate AND enddate
                THEN 'Yes' 
                ELSE 'No'
                END AS isAnniversary
    from   date_range 
           cross apply
           (
               select bookdate = DATEFROMPARTS(year(startdate), month(startdate), day(@bookdate))
           ) b
    

    Solution 2: find 1st of the month for startdate. Use that as base date and find no of days from that base date and use that for comparision.

    one special case is when bookdate is Jan-31 and start/end date is Feb-28 to Mar-01. That is handle with the OR condition in the query

    select *,
           CASE WHEN bookday BETWEEN startday AND endday
                OR   (bookday >= startday AND startmth <> endmth)
                THEN 'Yes' 
                ELSE 'No'
                END AS isAnniversary
    from   date_range 
           cross apply
           (
               select bookday  = day(@bookdate),
                      startday = day(startdate),
                      startmth = month(startdate),
                      endday   = datediff(day, dateadd(month, datediff(month, 0, startdate), 0), enddate) + 1,
                      endmth   = month(enddate)
           ) b