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

1
votes

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

2
sqlsql-serverdateadd
So you need to get a return of all dates between two dates? Is that what you are trying to do? – Sean Lange
Create a table called calendar and populate it with dates from 1900-01-01 to 2079-12-31 (or similar). Then you can just do SELECT * FROM calendar WHERE calendar_date BETWEEN x AND y... Do something similar for hours. – MatBailie
Between two dates, yes. Something like select YEAR, MONTH, DAY of each date between '2018/1/1' and '2018/5/1' – Zemmels

2 Answers

1
votes

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;
0
votes

This is where I go to a tally or numbers table. It is perfect for this kind of thing. I keep a tally table on my system as a view like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

GO

Now you have a way to query up to 10,000 sequential numbers instantly. But how we leverage this for a question like yours? 

declare @StartDate datetime = '20180501'
    , @EndDate datetime = '20180520'

select dateadd(day, t.N - 1, @StartDate)
from cteTally t
where t.N <= datediff(day, @StartDate, @EndDate) + 1
order by t.N

--EDIT-- It is not really clear if you want hours or days. But if you want hours simply change the DATEDIFF function to hour instead of day.