Showing the data with date information as the previous day before a certain time in SQL

I have a dataset with date information in MsSQL. The type of this data is registered as datetime. For example I have a date value like ‘2022-12-12 06:59:00’. Here, if the time is before 07 am, I want it to write ‘2022/12/11’ as the date. If the date value ‘2022-12-12 07:01:00’ is after 07 am I want it to print ‘2022/12/11’ as the date. To be more descriptive, I would like to explain as follows:

Source	Message	TimeStamp
Alarm	Message	2022-12-12 06:59:00.00000
Alarm2	Message2	2022-12-12 07:01:00.00000

This is the current table

I am trying to create the following table:

Source	Message	TimeStamp	Date
Alarm	Message	2022-12-12 06:59:00.00000	2022/12/11
Alarm2	Message2	2022-12-12 07:01:00.00000	2022/12/12

How can I do this in SQL query?

Answer

You can subtract 7 hours from TimeStamp and get the date part only from the result:

SELECT *,
       CONVERT(date, DATEADD(HOUR, -7, [TimeStamp])) AS [Date]
FROM tablename;

See the demo.

Advertisement

Answer