A way to extract from a DateTime value data without seconds

Question

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)

Answer 1

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

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

Answer 2

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.