SQL to SELECT JOIN to year, month, day between two dates

Question

I am working with joining two tables with YEAR, MONTH and DAY between two dates broken out in the query results. Table A may have some dates that Table B does not, but I need to show null/0 results regardless if there is a date to join to or not.

I'm looking for guidance on creating a separate query (or function) that takes Start and End dates, breaks them out to YEAR, MONTH, DAY, so I can use that as my base to join to the other two tables.

My request is somewhat like this function I found that returns all the hours for a specified date. I need all the year, month, day between two dates.

declare @date datetime = '1/1/2018'
select  dateadd(hour, number, cast(floor(cast(@date as float)) as datetime)) as StartHour, 
        dateadd(hour, number+1, cast(floor(cast(@date as float)) as datetime)) as EndHour 
from master.dbo.spt_values 
where number < 24 and type = 'p

StartHour               EndHour
2018-01-01 00:00:00.000 2018-01-01 01:00:00.000
2018-01-01 01:00:00.000 2018-01-01 02:00:00.000
2018-01-01 02:00:00.000 2018-01-01 03:00:00.000
...
2018-01-01 22:00:00.000 2018-01-01 23:00:00.000
2018-01-01 23:00:00.000 2018-01-02 00:00:00.000

Thanks in advance! Dan

Answer 1

You can create a 'tally' table for your dates on the fly like this:

DECLARE @start DATETIME='20000101';
DECLARE @end DATETIME='20180517';

WITH
    [days] AS (
                  SELECT TOP(DATEDIFF(d, @start, @end)+1)ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
                  FROM master.sys.all_columns t1
                       CROSS JOIN master.sys.all_columns t2
              ),
    tally(myDate) AS (SELECT DATEADD(d, [days].N-1, @start)FROM [days])
SELECT * FROM tally;