Generate range of dates based on other table

• 
  Hi!
  
  There are probably a number of different ways of doing this, but here's one off the top of my head.
  
  Suppose that we have a table named "dates", which contains two columns - "startdate" and "enddate". This is where we'll get the start and end dates from. Now, for the column we want to generate the dates for, choose a "SQL Statement" generator and set the connection to point to your database. Then you can put something like this in the SQL Source textbox -
  

  DECLARE @StartDateTime DATE
  DECLARE @EndDateTime DATE
  
  SET @StartDateTime = (SELECT TOP 1 startdate FROM dates);
  SET @EndDateTime = (SELECT TOP 1 enddate FROM dates);
  
  WITH DateRange(DateData) AS 
  (
      SELECT @StartDateTime as Date
      UNION ALL
      SELECT DATEADD(d,1,DateData)
      FROM DateRange 
      WHERE DateData < @EndDateTime
  )
  SELECT DateData
  FROM DateRange
  OPTION (MAXRECURSION 0)
  

  
  This will generate all the dates in between the values stored in the dates table. There may well be a better SQL query to do this - I put it together quite quickly.
  
  I hope this helps!

  Rob C January 11, 2016 11:05

  - 0 +
• 
  Thank you very much:)

  baron pampa January 12, 2016 02:58

  - 0 +

Add comment

Please sign in to leave a comment.