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!
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
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;