CODEWITHSUNDEEP
sqlt-sqlazure-sql-databasetruncate
Narek Nersisyan
Narek Nersisyan

Reputation: 75

Truncating dates in Microsoft Azure SQL?

Suppose I have a timestamp and I would like to truncate this timestamp to minutes since the Sunday of the week the timestamp was in. In Standard SQL I would do something like:

timestamp_diff(created_at, timestamp_trunc(created_at, week), minute) where the creared_at would be the timestamp that I have and week would be a parameter that the truncate function would take to truncate it to the week. How would this be possible to do in Azure?

Upvotes: 0

Views: 1418

Answers (1)

ashja99
ashja99

Reputation: 388

I'm going to answer this on the assumption that by "timestamp" you mean a datetime value. The actual timestamp datatype in Sql Server (and Azure) is a deprecated synonym of the rowversion datatype, and has nothing to do with actual dates or times.

If you are dealing with a datetime value, the following will give you the number of minutes between @timestamp_to_truncate and the previous Sunday at 12am:

declare @timestamp_to_truncate datetime = getdate()

declare @sunday datetime = cast(dateadd(day,1-DATEPART(dw, @timestamp_to_truncate),cast(@timestamp_to_truncate as date)) as datetime)

select datediff(mi,@sunday,@timestamp_to_truncate)

Upvotes: 1

Related Questions