Select Statement Gathering Dates

Question

I have a form where I submit a Start and End date to book of holidays, I then send the value's across to SQL, now i'm a bit stuck because what I need to do is get the dates between the start and end date.

Can anyone help me with this I just need a calculation for my select statement to transfer all the dates between and on the start and end date.

Thanks in advance to your answers/replies :)

Answer 1

Try this:

DECLARE @FromDate datetime
DECLARE @ToDate datetime
SELECT @FromDate=FromDateCol FROM TableName
SELECT @ToDate=ToDateCol FROM TableName
WITH cte AS
(
  SELECT CAST(@FromDate AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    cte   
  WHERE   DateValue + 1 < @ToDate
)

SELECT  DateValue
FROM    cte
OPTION (MAXRECURSION 0)