CODEWITHSUNDEEP
sqlsql-serverdatetimestored-procedures
AdzzzUK
AdzzzUK

Reputation: 298

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.

Upvotes: 1

Views: 220

Answers (2)

George Menoutis
George Menoutis

Reputation: 7240

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

Upvotes: 2

Umair Rasheed
Umair Rasheed

Reputation: 448

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

Upvotes: 2

Related Questions