Identify each hour between start and end time

Not really sure how to describe this, but I'll give it a shot.

Background

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.

Desired Result

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.

2 answers

  • answered 2018-10-17 07:59 George Menoutis

    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
    

    Note the ; before the WITH

  • answered 2018-10-17 08:02 Umair Rasheed

    Your Date Difference For Hours

    1. select DATEDIFF(HOUR,'Your Start Date','Your End Date') from Table_1
    2. select DATEDIFF(HOUR,'2018-10-19','2018-10-20') from Table_1