Check month and day fields lies between two dates

Hi Sarayu_CM,

If I understand correctly, you want to select month and day fields with this format (MM/DD) between two dates.

The following query is for your reference:

CREATE TABLE #T (
 MyMonth INT
 ,Myday INT
 )
INSERT #T
select 2,11 union
select 4,11 union
select 2, 15 union
select 1, 20 
DECLARE @StartDate DATE,@EndDate DATE,@mymonthday date
SET @StartDate = '02/11/2012'
SET @EndDate = '02/28/2014'
declare @year int,@start int,@end int
set @year=0
set @start=year(@StartDate)
set @end=year(@EndDate)
;with cte as 
(select mymonth, myday, @start as runningvalue
from #T 
union all
select t.mymonth,t.myday,c.runningvalue+1
from cte c 
inner join #T t on c.MyMonth=t.MyMonth
where c.runningvalue+1<=@end)
select 
distinct (case 
when Mymonth>=10
then cast(mymonth as varchar(2))+'/'+cast(Myday as varchar(2))
when MyMonth<10
then '0'+cast(mymonth as varchar(2))+'/'+cast(Myday as varchar(2))
end) mymonthday from cte 
WHERE  cast(((case 
when Mymonth>=10
then cast(mymonth as varchar(2))+'/'+cast(Myday as varchar(2))
when MyMonth<10
then '0'+cast(mymonth as varchar(2))+'/'+cast(Myday as varchar(2))
end)+'/'+cast(runningvalue as varchar(4))) as date) between @StartDate and @EndDate

Thanks,

Katherine Xiong

---

Katherine Xiong

TechNet Community Support