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

Question

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;

Answer 1

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