List of dates between two dates

Manoj Sevda
13y
11.9k
0
0

Blog

declare @stdt datetime
       ,@enddt datetime

select @stdt = '2012-01-01'
       ,@enddt = '2012-12-31'

select ROW_NUMBER() OVER (ORDER BY dateadd(day, nu.number, @stDt)) AS 'RowNumber', dateadd(day, nu.number, @stDt)as mDate, DATENAME(DW, dateadd(day, nu.number, @stDt)) as mDay,
       DATENAME(month,dateadd(day, nu.number, @stDt)) as mMonth

from   master..spt_values nu --Any Table of consecutive integers
left outer join sysobjects o --YourTable
       on o.crdate = dateadd(day, nu.number, @stDt)
where nu.type = 'p'
and    nu.number <= datediff(day, @stDt, @endDt)
group by dateadd(day, nu.number, @stDt)
order by mDate