A way to extract from a DateTime value data without seconds

I have an sql DateTime (ms sql server) and want to extract the same date without the seconds: e.g. 2011-11-22 12:14:58.000 to become: 2011-11-22 12:14:00.000

How can I do this? I was thinking to use DATEADD in combination with DATEPART but seems very error prone (besides performance issues)

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, yourcolumn), 0) FROM yourtable

This will be effective, if you don't want a slow conversion between datatypes.

For a solution that truncates using strings try this:

SELECT CAST(CONVERT(CHAR(16), GetDate(),20) AS datetime)

CHAR(16) works only if our variable is converted to ODBC canonical format, as shown above by using 20 as the format specifier.

DECLARE @date DateTime = '2011 Nov 22 12:14:55';
SELECT CONVERT(Char(16), @date ,20) AS datetime

Results:

| datetime         |
|------------------|
| 2011-11-22 12:14 |

Then you simply cast back to a DateTime type to continue using the value.

NOTE: This is only viable for data types that do not carry TimeZone info. Also type conversions to VarChar and back are usually LESS performant than using DateTime functions that use numeric operations internally.

Consider other solutions posted if performance is a concern or if you must retain timezone information.

DECLARE @TheDate DATETIME
SET @TheDate = '2011-11-22 12:14:58.000'

DATEADD(mi, DATEDIFF(mi, 0, @TheDate), 0)

In queries

/* ...all records in that minute; index-friendly expression */ 
WHERE TheDate BETWEEN DATEADD(mi, DATEDIFF(mi, 0, @TheDate), 0) 
                  AND DATEADD(mi, DATEDIFF(mi, 0, @TheDate) + 1, 0)

Date and time needs carefully and not being converted as TEXT.

My personal solution:

    CREATE FUNCTION [dbo].[fnDateTimeTruncated]
(
    @datetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
    RETURN DATETIMEFROMPARTS ( year(@datetime), month(@datetime), day(@datetime), DATEPART(hh,@datetime), DATEPART(mi,@datetime), 0, 0)
END

Edited:

Regarding http://blog.waynesheffield.com/wayne/archive/2012/03/truncate-a-date-time-to-different-part/, DateAdd has a better performance. Thanks to t-clausen.dk

With a little fiddling around, this seems to work well:

SELECT CAST(CONVERT(CHAR(17), bl.[time],113) AS varchar(17))

Result given: 2011-11-22 12:14

The exact way I'm using it in my query as part of the selection list :

,CAST(CONVERT(CHAR(17), bl.[time],113) AS varchar(17))
+ '    (UTC +0)' AS [TIME]

Gives me the result: 15 Dec 2017 06:43 (UTC +0)

A way to extract from a DateTime value data without seconds

Related

Recent Posts