List of dates between two dates

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

Capture1.PNG





Ebook Download
View all
Learn
View all