Add a hour column and offset columns to Date table in SQL

Question

Hi I am trying to write a T-SQL view, to add all 24 hours for each day as a column and some offset columns to the existing date table. Below is the current format how data is

Below is the data format and columns I am trying to add

1. If hour of day is current hour then columns 'Hour of Day' and 'Hour Offset' should show '0'.
   • All the future hours should be positive increments till end of current day.
   • All the past hours should be negative increments till start of the day.
2. If the date is in current week then week offset should be '0' and week start should be Monday.

Please advise

Answer 1

To span the hour you will need cross join your date table like this:

FROM dates D
CROSS JOIN (VALUES(0), (1), (3), ..., (23)) AS H(Hour)

For most of the offsets, they can be computed straightforwardly with datediff. However, for the week offset, you will need to compensate for the fact that datediff always consider Sunday to be the first day of the week.

Answer 2

Hi,@Learner DAX

week start should be Monday

You need to add this statement :

SET DATEFIRST  1       -- 1 => Monday, 7 => Sunday  

Please check this :

SET DATEFIRST  7-- 1 = Monday, 7 = Sunday  
declare @date datetime ='1/13/2022 10:00'  
select Date+' 0:00' as [Date],Date+' '+cast(time as varchar)+':00' as [Hour],  
       datediff(d,@date,Date) as 'Date offset',  
       datediff(hh,@date,Date+' '+cast(time as varchar)+':00') as 'Hour offset',  
	   time-datepart(hour,@date) as 'Hour of day offset',  
	   datediff(m,getdate(),@date) as 'Month offset',  
	   datediff(yy,getdate(),@date) as 'Year offset',  
	   datediff(wk,getdate(),@date) as 'Week offset'  
from  #date D CROSS JOIN (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23))as T(time)  

Something I am not sure is the difference between 'Hour of Day Offset' and 'Hour Offset'.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi,@Learner DAX
As Erland commented:

it turns out that datediff(WEEK) ignores DATEFIRST, and always go by Sunday

So,we can't use datediff(WEEK) to get correct 'Week offset'.
We need to consider another method, the test is as follows:

SET DATEFIRST 1 -- 1 = Monday, 7 = Sunday
IF OBJECT_ID('view_test')IS NOT NULL
DROP VIEW view_test
GO
create view view_test as
select ThedayofWeek = DATEPART(Weekday, getdate()),
datediff(wk,getdate(),'1/16/2022') as 'Week offset_A',
datepart(week,'1/16/2022') - datepart(week,getdate()) as 'Week offset_B'
GO
select * from view_test

Output:
![165916-image.png][1]

Best regards,
LiHong