SQL query - Get records between same day-month period but year-CodePudding

I have a table with records from 2010 until now with a datetime field. Need to find records in same period over the years.

Example of what I need: Rows that are between two dates(D1 and D2) that came from an input form: 11-15(November 15) and 01-15(January 15, next year) 2021-11-15 to 2022-01-15 2020-11-15 to 2021-01-15 2019-11-15 to 2020-01-15 and so on....

Additional info: Search dates came from a form so it is not always same. If date period happens to be in same year my query works.

What i have tried:

SELECT * FROM my_table  WHERE (DATEPART(MONTH, date_field)>=11 
AND DATEPART(DAY, date_field)>=15) 
AND (DATEPART(MONTH, date_field)<=1 
AND DATEPART(DAY, datefield)<=15)

But obviously it does not work.....

Any ideas ?

CodePudding user response：

You can do it with a tally

with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
), tally as(
   select top(13) 2009   row_number() over(order by t1.n)  year
   from t0 t1, t0 t2, t0 t3
)
select m.*
from tally t
join mytable m on m.date_field >= datefromparts(t.year, 11, 15)
     and date_field < datefromparts(t.year   1, 1, 16);

CodePudding user response：

You need to change your where condition to yield expected records

since you want records within a certain day/month range, The records can fall within either one criterion or other, because of AND the records fail to satisfy the where clause hence nothing is fetched

SELECT * FROM my_table

WHERE ( DATEPART(MONTH, date_field) >=11 AND DATEPART(DAY, date_field)>=15

OR ( DATEPART(MONTH, date_field) <=1 AND DATEPART(DAY, datefield)<=15)

this should do the trick