Create Date List from Start Date and End Date (T-SQL)



DECLARE @startdate DATETIME, @enddate DATETIME;
SELECT @startdate = '2012-01-01',  @enddate = '2012-01-13';
  
--**** you can create a calendar table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)
  
,myDateCTE as
(
select dateadd(day,n-1,@startdate) dt 
from Nums
 
)
 select dt from myDateCTE
WHERE dt<=@enddate


----Or use a values constructor to build a number list

;with mycte as (
select dateadd(day, n-1,dt) dt2 from (select @startdate as dt ) t
cross apply(values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)) d(n)
)
Select * from mycte
WHERE dt2<=@enddate

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s