Return Select with list of dates between 2 dates

• amackay_1

  Ten Centuries

  Points: 1240

  More actions

  March 29, 2006 at 9:01 am

  #152867

  Hi,  I would like to create a stored procedure that when 2 dates are passed in, it returns a select with a list of the dates between the 2 dates passed in.

  For example:

  @StartDate='1/1/06'

  @EndDate='1/4/06'

  Would return

  DATEOUT

  1/1/06

  1/2/06

  1/3/06

  1/4/06

  Is there are way to do this without looping or cursors?

  Andrew

   

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 29, 2006 at 9:21 am

  #629532

  Here's one way...

  --data

  declare @StartDate datetime

  declare @EndDate datetime

  set dateformat mdy

  set @StartDate='1/1/06'

  set @EndDate='1/4/06'

  --numbers table

  declare @Numbers table (i int identity(0, 1), j bit)

  insert @Numbers select top 10000 null from master.dbo.syscolumns a, master.dbo.syscolumns b

  --calculation

  select dateadd(d, i, @StartDate) as Date from @Numbers where dateadd(d, i, @StartDate) <= @EndDate

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• amackay_1

  Ten Centuries

  Points: 1240

  More actions

  March 29, 2006 at 9:29 am

  #629540

  Thanks Ryan, works great!