creating a table of dates on the "fly" 1

[pandatime]

Hi,

Is there a way to create a table of dates on the fly *without* using a looping structure?

Basically, what I want to do is create & load a table of dates for the past 90 days (based on GETDATE()). It will run every day and create new dates for the past 90 days.

I wrote a little looping structure to do this and it works fine but just wanted to know if there are other methods that don't involve looping.

Thanks

 

[gmmastros]

try this:

Select Top 90 DateAdd(Day, -Number, DateDiff(Day, 0, GetDate()))
From   Master..spt_Values
Where	Type = 'P'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

While George's answer is clean and easy I thought this might also be a good opportunity to show a CTE example for the above situation, as many people still don't know how to work with them.

with tbl1 (date1, days) as (
  select DATEADD(d,-0,cast(getdate() as DATE)), 1 as days
  union all
  select DATEADD(d,-days,cast(getdate() as DATE)), days + 1
  from tbl1
  where days < 90)
select date1 from tbl1

Not a clean one, and maybe the days could be worked differently, but it will give all 90 days prior to and including the current date.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886