A way to extract from a DateTime value data without seconds

39
votes

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)

8
sqlsql-serverdatetime
In what way does this seem error-prone? – user359040
long and quite hard to write correctly ? :-) – Ghita
is it that you want to hide seconds?? why are you providing seconds as 000 ALWAYS?? – Fahim Parkar
@Fahim I want to have 0 seconds because I want to group records from the same date/hour/minute – Ghita

8 Answers

25
votes

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.

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

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

2
votes
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)
2
votes

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

2
votes

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)

1
votes

If there is no milliseconds, than

DECLARE @dt datetime2 = '2011-11-22 12:14:58.000';
DECLARE @goalDt datetime2 = DATEADD(second,-DATEPART(second,@dt), @dt);

To remove a milliseconds part, add 

SET @goalDt = DATEADD(millisecond,-DATEPART(millisecond,@goalDt ), goalDt dt);
1
votes

To Round Off it:

DECLARE @TheDate DATETIME;
SET @TheDate = '2019-1-2 12:14:58.400';

SELECT CAST(@TheDate AS SMALLDATETIME);

To just Truncate: 

DECLARE @TruncTheDate DATETIME;
SET @TruncTheDate = '2019-1-2 12:14:58.400';

SELECT DATEADD(mi, DATEDIFF(mi, 0, @TruncTheDate), 0);
1
votes

From SQL Server 2014, You can use Format function for this.

for Ex.

 declare @Startdate datetime = '2020-11-07 15:27:50.713'

 set  @Startdate = Convert(datetime,FORMAT(@Startdate, 'yyyy-MM-dd HH:mm'))


> Result is 
2020-11-07 15:27:00.000