Identify each hour between start and end time
Not really sure how to describe this, but I'll give it a shot.
I need to backfill some historical manufacturing data into a data warehouse, and will be recording this per hour for each of the production lines. This will be a one-time activity, rather than an ongoing process.
I need to create a table variable which contains the breakdown of each hour, ie
Row ID | Start Time 1 | 2018-01-01 00:00:00 2 | 2018-01-01 01:00:00 ...etc... nn | 2018-10-12 10:00:00
If I can get the hours out for between two points in time into a table (which I have defined), then I can run the relevant routines to backfill the data. I'm sure this is fairly simple to achieve - can anyone give me any pointers please?
Thanks in advance.
This solution uses recursion:
DECLARE @starttime datetime='2018-01-01 00:00:00' DECLARE @endtime datetime='2018-01-02 00:00:00' ;WITH hour_tbl(row_id,hour_value) AS ( SELECT 1,@starttime -- Seed Row UNION ALL SELECT row_id+1,dateadd(hour,1,hour_value) -- Recursion FROM hour_tbl WHERE dateadd(hour,1,hour_value)<=@endtime ) SELECT * FROM hour_tbl
Your Date Difference For Hours
- select DATEDIFF(HOUR,'Your Start Date','Your End Date') from Table_1
- select DATEDIFF(HOUR,'2018-10-19','2018-10-20') from Table_1