How to populate a table with dates

_{Thursday, October 13, 2011 7:01 PM}

hi there, 

I'm looking to create a temp table and then populate it with date range. something like @StartDate = 01/01/11 and @EndDate = 01/31/11

So I'm looking to insert 31 dates from Jan/01/11 to Jan/31/11

Any ideas?

Thanks,

Manny

_{Thursday, October 13, 2011 7:15 PM ✅Answered}

create table #temp(d date)

declare @sdate datetime='2011-01-01 00:00'

declare @edate datetime='2011-01-31 00:00'

insert into #temp

select DATEADD(d,number,@sdate) from master..spt_values where type='P' and number<=datediff(d,@sdate,@edate)

select * from #temp

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

_{Thursday, October 13, 2011 7:08 PM}

You can have a Calendar table and use it for any purpose that you want.

http://arbibaghdanian.blogspot.com/2011/05/calendar-table.html

Best Wishes, Arbi MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

_{Thursday, October 13, 2011 7:12 PM}

You can use an auxiliary table of numbers (Itzik's VATN is a nice one), a recursive cte, etc, and of course a calendar table as suggested by Arbi.

select dateadd([day], n - 1, @startdate) as dt
from dbo.GetNums(datediff([day], @startdate, @enddate) + 1)
order by dt;

with rs as (
select @startdate as dt
union all
select S.dt
from rs cross apply (select dateadd([day], 1, dt) as dt) as S
where S.dt <= @enddate
)
select dt
from rs
order by dt;

AMB

Some guidelines for posting questions...

_{Thursday, October 13, 2011 8:12 PM}

Thanks v.vt, that did the trick.

_{Friday, October 14, 2011 3:25 AM}

Please learn to use the ISO-8601 date format.

In SQL, we use an idiom of a Calendar table which has all of the temporal data we need for the enterprise. Google it if you are that new to RDBMS.  We would never think of using computations and materializing a temp table; that is classic procedural coding and has not place in a declarative language. SQL Programmers would write a VIEW, CTE or derived table like this:

 (SELECT cal_date

      FROM Calendar

  WHERE cal_date BETWEEN @in_start_date AND @in_end_date)

AS  Foobar(foo_date)

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL