CODEWITHSUNDEEP
sql-servert-sql
Jason Horner
Jason Horner

Reputation: 3690

T-SQL truncate time (not round) to the nearest minute

Looking for the most efficient and elegant way to do truncate the time to the minute

-- I need to truncate the time to the minute, 
-- this code almost works but rounds up 
SELECT
    CAST('2021-09-02T15:15:30.9233333' AS datetime2(7)) AS EventDatetime2,
    CAST(CAST('2021-09-02T15:15:30.9233333' AS datetime2(7)) AS TIME(0)) AS EventTime

Upvotes: 2

Views: 830

Answers (4)

paneerakbari
paneerakbari

Reputation: 725

Combining the DATEADD and CAST(... AS SMALLDATETIME) approaches effectively gives you a minute "floor", like so:

SELECT CAST('2021-09-02T15:15:30.9233333' AS DATETIME2(7))                  AS EventDatetime2,
       CAST(CAST('2021-09-02T15:15:30.9233333' AS DATETIME2(7)) AS TIME(0)) AS EventTime,
       CAST(CAST(DATEADD(
                SECOND,
                (DATEPART(SECOND, CAST('2021-09-02T15:15:30.9233333' AS DATETIME2(7))) * -1),
                CAST('2021-09-02T15:15:30.9233333' AS DATETIME2(7))) AS SMALLDATETIME) AS TIME(0));
EventDatetime2 EventTime (No column name)
2021-09-02 15:15:30.9233333 15:15:31 15:15:00

The DATEADD in this example subtracts the number of seconds from the datetime before converting it to a smalldatetime, so when that cast/convert does its rounding it will always go to the lower minute.

If, however, your input value is the sort of string literal that the wording of your question implies, you could also do this:

SELECT CAST(SUBSTRING('2021-09-02T15:15:30.9233333', CHARINDEX('T', '2021-09-02T15:15:30.9233333')+1, 6) + '00' AS TIME(0));

and get this result:

15:15:00

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Just another option using left() and the implicit conversion.

Depending on the actual USE CASE, the outer convert() is optional

Example

DECLARE @dt datetime2(7) = '2021-09-02T15:15:30.9233333';

select convert(smalldatetime,left(@dt,16))

Results

2021-09-02 15:15:00

Upvotes: 1

anon
anon

Reputation:

As Larnu posted, if you want to round up or down depending on the seconds value, a simple convert to smalldatetime will do.

If you want to truncate, there are several ways, the simplest is probably just to add minutes to midnight (only posting because I prefer without the magic dates like 1900-01-01):

DECLARE @dt datetime2(7) = '2021-09-02T15:15:30.9233333';
DECLARE @d  datetime2(7) = CONVERT(date, @dt);

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, @d, @dt), @d);

Another way is more intuitive but a little ugly:

DECLARE @dt datetime2(7) = '2021-09-02T15:15:30.9233333';

SELECT SMALLDATETIMEFROMPARTS
(
  DATEPART(YEAR,   @dt), 
  DATEPART(MONTH,  @dt), 
  DATEPART(DAY,    @dt), 
  DATEPART(HOUR,   @dt), 
  DATEPART(MINUTE, @dt)
);

Upvotes: 5

Thom A
Thom A

Reputation: 95564

If you want to "round" to the nearest minute you could just CONVERT the value to a smalldatetime; they are only accurate to 1 minute:

SELECT CONVERT(smalldatetime,CONVERT(datetime2,'2021-09-02T15:15:30.9233333'));

If you want to, you can then CONVERT back to your original data type.

If you want to truncate (so strip the minutes) you could use the old DATEDIFF and DATEADD method:

DECLARE @DateTime2 datetime2(7) = '2021-09-02T15:15:30.9233333';
SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,'19000101',@DateTime2),CONVERT(datetime2(7),'19000101'));

Upvotes: 2

Related Questions