1

I hope someone has a quick answer for this...

I have a start date and an end date. What's the quickest/easiest way to produce a complete list of all included days?

startDate         endDate
------------------------------
2013-01-01        2013-01-07

would produce:

day        date
--------------------
 1        2013-01-01
 2        2013-01-02
 3        2013-01-03
 4        2013-01-04
 5        2013-01-05
 6        2013-01-06
 7        2013-01-07

Thank you for your kind attention! :)

jlisham
  • 144
  • 1
  • 11
  • Already answered here: http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates – jpw Oct 10 '13 at 19:49
  • 1
    @AaronBertrand The answers to the protected post includes solutions for MSSQL as well, see the 2nd answer for example. – jpw Oct 10 '13 at 19:51
  • @jpw the question is tagged MySQL, so just because someone answered with the wrong RDBMS doesn't make it a proper duplicate IMHO. – Aaron Bertrand Oct 10 '13 at 19:52
  • @AaronBertrand True, I just wanted to indicate where the OP could find an answer... – jpw Oct 10 '13 at 19:54

1 Answers1

1

This will support up over 2,000 days (depending on your version of SQL Server and how many user objects you have), if you need more than that, just change sys.all_objects to sys.all_columns (which should support over 8,000).

DECLARE @start DATE = '20130101', @end DATE = '20130107';

;WITH x AS
(
  SELECT TOP (DATEDIFF(DAY, @start, @end)+1) 
    rn = ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.all_objects
)
SELECT [day] = rn, [date] = DATEADD(DAY, rn-1, @start)
FROM x
ORDER BY rn;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • both responses were very helpful - thank you, @jpw for pointing me to the procedure post and thank you for the answer in both that post and this, @aaronBertrand....stellar! – jlisham Oct 10 '13 at 20:01