A way to extract from a DateTime value data without seconds

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

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)