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)

is it that you want to hide seconds?? why are you providing seconds as 000 ALWAYS?? — Fahim Parkar, Jan 17 '12 at 14:55
@Fahim I want to have 0 seconds because I want to group records from the same date/hour/minute — Ghita, Jan 17 '12 at 15:16

t-clausen.dk · Answer 1 · 2017-12-14T06:41:28.283

71

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

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

edited Dec 14 '17 at 06:41

answered Jan 17 '12 at 15:16

t-clausen.dk

43,517
12
59
92

4

Regarding the "Edit, best solution", casting like that will round to the nearest minute. OP wants the seconds truncated, not rounded. Example showing the rounded result: `DECLARE @myDate DATETIME = '2007-05-08 12:35:32' SELECT CAST(@myDate AS smalldatetime)` – Ojen Feb 12 '15 at 21:09
2

@Ojen you are absolutely right, well spotted. I removed that part of my answer – t-clausen.dk Feb 12 '15 at 21:56

score 28 · Accepted Answer · edited Dec 19 '19 at 01:22

28

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.

edited Dec 19 '19 at 01:22

Chris Schaller

13,704
3
43
81

answered Jan 17 '12 at 14:55

Rob

3,026
4
30
32

10

This isn't a good answer. First of all, `CHAR(17)` has to be used instead, or the minutes will be wrong. Secondly, this method produces a type conversion warning. – mbomb007 Nov 21 '17 at 16:17
I just wasted 1 hour of work asuming this query was correct :p Thank you, internet! – Stephan Møller Jan 28 '19 at 12:37
1

This answer is correct when using the input string shown in the example. It is however not correct if the field we are operating on is a DateTime object. – Chris Schaller Dec 19 '19 at 00:56
It's a fairly critical weakness not to be able to cope with a datetime. The text conversion is a starting point, but we need a more general solution. And it won't let me take back my upvote. – Dodecaphone Aug 17 '20 at 14:24
Fellow seekers, keep scrolling to t-clausen.dk's solution below. – Dodecaphone Aug 17 '20 at 14:28

score 3 · Answer 3 · answered Jan 17 '12 at 14:57

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)

Fernando Angelieri · Answer 4 · 2016-12-28T20:33:57.103

2

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

edited Dec 28 '16 at 20:33

answered May 03 '15 at 17:26

Fernando Angelieri

111
7

1

DateTimeFromParts is not an effective solution. Found this [Link](http://blog.waynesheffield.com/wayne/archive/2012/03/truncate-a-date-time-to-different-part/) – t-clausen.dk Dec 05 '16 at 08:33
Thanks for the info Clausen. – Fernando Angelieri Dec 28 '16 at 20:29

score 2 · Answer 5 · edited Dec 15 '17 at 08:16

2

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)

edited Dec 15 '17 at 08:16

Nawrez

3,314
8
28
42

answered Dec 15 '17 at 07:49

Jason Hanwith-Horden

21
1

`,CAST(CONVERT(CHAR(17), bl.[time],113) AS varchar(17))` is the solution to the result (`15 Dec 2017 06:43`) I was looking for. – seantunwin Jun 17 '19 at 17:41

Bhadresh Patel · Answer 6 · 2020-11-09T12:13:35.727

2

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

edited Nov 09 '20 at 12:13

answered Nov 07 '20 at 09:58

Bhadresh Patel

1,671
17
18

1

Nice Option. But this should be used for small tables only. In larger tables, as in my case with 10 Million Rows with Index on datetime variable, this increased the query time from 2 seconds to 23. More here: https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but – Dilnoor Singh Apr 17 '21 at 22:25

Kosix · Answer 7 · 2014-02-27T13:29:43.860

1

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);

edited Feb 27 '14 at 13:29

answered Feb 27 '14 at 12:42

Kosix

111
1
3

score 1 · Answer 8 · edited Apr 24 '20 at 15:25

1

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);

edited Apr 24 '20 at 15:25

Stewart

3,935
4
27
36

answered Apr 30 '19 at 14:28

prakash chourasia

11
1

score 0 · Answer 9 · answered Mar 08 '22 at 07:06

0

select substring(cast(cast(getdate() as time(0)) as char(8)),0,6)

answered Mar 08 '22 at 07:06

nimajv

423
3
11

score 0 · Answer 10 · answered Jun 14 '23 at 02:22

0

DECLARE @now DATETIME = GETUTCDATE()
SELECT @now = DATETIMEFROMPARTS(DATEPART(YEAR, @now),
                                DATEPART(MONTH, @now),
                                DATEPART(DAY, @now),
                                DATEPART(HOUR, @now),
                                DATEPART(MINUTE, @now),
                                0,
                                0)

answered Jun 14 '23 at 02:22

user2787130

11
1

1

You should add explanation. – Super Kai - Kazuya Ito Jun 14 '23 at 05:58

A way to extract from a DateTime value data without seconds

10 Answers10

Linked

Related