Home > Back-end >  Showing the data with date information as the previous day before a certain time in SQL
Showing the data with date information as the previous day before a certain time in SQL

Time:12-23

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?

CodePudding user response:

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.

  • Related