SQLTeam.com | Weblogs | Forums

Find dates with a date range


#1

Hello,

I have a table with 2 dates, Start Date and End Date. I need to find all dates within the range between Start Date and End Date. So if the start date is 05/01/2016 and the end date is 05/06/2016 the output should be
05/01/2016
05/02/2016
05/03/2016
05/04/2016
05/05/2016
05/06/2016

The database i am working with has a table called Date dimension. This lists all dates from 1850 to sometime in the future. I was wondering if I could use a CTE with this table to find these dates or if there is a better way.

Thank you!


#2

You can do this
Select fields from table
Where startDate='20160501' and EndDate='20160506'

If you want to create a CTE just put the query inside

With myCTE
As
(
Select fields from table
Where startDate='20160501' and EndDate='20160506'
)
Select * from myCTE


#3

When you say CTE, if you meant recursive CTE, you can certainly do that, but it would be inefficient and unnecessary in this case. You can do a simple join like shown below:

SELECT
	a.StartDate,
	a.EndDate,
	d.Date
FROM
	YourTable a
	INNER JOIN DimDate d ON
		d.Date >= a.StartDate
		AND d.Date <= a.EndDate;