CODEWITHSUNDEEP
sql-server
joe
joe

Reputation: 21

Offset time value of Datetime string

I have a timestamp in a table in the format '2020-09-29 06:30:00.000'. I need to get a new row called "ProductionDate" from this timestamp column. If the timestamp minutes are < 06:30:00.000, i need to to roll back to the previous date as the Production date.

For example

'2020-09-29 06:30:00.000' - '2020-09-29 14:30:00.000' is 1st shift. 
'2020-09-29 14:30:00.000' - '2020-09-29 22:30:00.000' is 2nd shift.
'2020-09-29 22:30:00.000' - '2020-09-30 06:30:00.000' is 3rd shift.

I would like to get a production date if the timestamp fall in this 24 hour range above to give me "2020-09-29".

I am currently getting date using the below command but it is going off of calendar day of the timestamp.

convert(date, c.[Timestamp]) as productiondate

Upvotes: 0

Views: 178

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Just subtract '06:30:00' from the date/time. I would do this as:

date(timestamp - interval (6*60 + 30) minute) as productiondate

You would use the same idea in SQL Server, but the syntax is different:

convert(date, dateadd(minute, - (6*60 + 30), timestamp)) as productiondate

Upvotes: 1

Related Questions