T-SQL to trim a datetime to the nearest date?

Question

Duplicate of What's the BEST way to remove the time portion of a datetime value (SQL Server)?

I have a column that tracks when things are created using a datetime, but I'd like to generate a report that groups them by day, so I need a way of nulling out the time component of a datetime column.

How do I do this?

score 42 · Answer 1 · edited Jul 01 '17 at 05:43

42

Why not convert straight to date:

select convert(date, getdate())

This truncates days, not rounds. To round Days do this:

select convert(date, getdate() + 0.5)

edited Jul 01 '17 at 05:43

jordanwillis

10,449
1
37
42

answered Jan 05 '12 at 20:24

pieman72

429
1
4
3

1

Easily the best answer for its simplicity and clarity. – Kirk Woll Apr 07 '12 at 18:28
6

Not if you are using SqlServer 2005 – TheMoot May 22 '12 at 18:39
@nalply because SQL Server 2005 doesn't have the *date* data type – John Pick Dec 29 '13 at 19:13

score 20 · Accepted Answer · edited May 21 '11 at 17:04

20

One way is to change getdate() to your column name,

select dateadd(dd, datediff(dd, 0, getdate())+0, 0)

edited May 21 '11 at 17:04

Peter Mortensen

30,738
21
105
131

answered Feb 11 '09 at 19:43

SQLMenace

132,095
25
206
225

I don't think the +0 is required (at least using sql server 2012+). This is the most creative and useful answer I've seen. – Rolan Apr 09 '18 at 07:55

score 5 · Answer 3 · answered Jul 27 '09 at 21:58

5

Here's another solution:

SELECT CAST( FLOOR( CAST( GETDATE() AS float) ) AS smalldatetime)

answered Jul 27 '09 at 21:58

richardtallent

34,724
14
83
123

score 3 · Answer 4 · edited Aug 22 '19 at 13:36

3

This is a simple way to get the date (as a string) from a datetime:

convert(varchar, <the date field/value/etc>, 101)

But note that ordering on this field will be alphabetical rather than by date since it's now a string

edited Aug 22 '19 at 13:36

Zach Smith

8,458
13
59
133

answered Feb 11 '09 at 19:40

notnot

4,472
12
46
57

score 1 · Answer 5 · answered May 17 '18 at 18:14

1

I simply do cast(getdate() as date)

answered May 17 '18 at 18:14

C_BB

11
1

Austin Salonen · Answer 6 · 2010-04-23T20:23:03.663

1

Yes. There are many formats to choose from so I'll link it instead.

http://library.cirr.com/Microsoft/SQL-Server-v7/html/ca-co_1.htm

If you wish to zero out the time like your post implies, you can try this:

select cast(convert(varchar, getdate(), 101) as datetime)

edited Apr 23 '10 at 20:23

answered Feb 11 '09 at 19:40

Austin Salonen

49,173
15
109
139

1

convert(datetime, convert(varchar, getdate(), 101)) – Gordon Bell Feb 11 '09 at 20:11
broken link. New link for the page http://library.cirr.com/Microsoft/SQL-Server-v7/html/ca-co_1.htm – monkeypushbutton Apr 23 '10 at 20:07

score 0 · Answer 7 · answered Feb 11 '09 at 19:43

0

declare @CurrentDate datetime
set @CurrentDate = dateadd(dd, datediff(dd, 0, getdate()), 0)

--or--

select dateadd(dd, datediff(dd, 0, MyDateColumn), 0) as DateOnly
from tblX

answered Feb 11 '09 at 19:43

Gordon Bell

13,337
3
45
64

Allain Lalonde · Answer 8 · 2009-02-11T22:54:32.553

0

In my searches I came across the following solution, it strips time out of UTC time only, but I found it interesting, so I thought someone else would too:

FUNCTION TrimDate(@dt AS DATETIME) RETURNS DATETIME
BEGIN
    RETURN CAST(CAST((@dt - 0.500000038580247) AS INT) AS DATETIME) 
END

I would presume that it runs quickly since all it's doing is rounding and casting.

edited Feb 11 '09 at 22:54

answered Feb 11 '09 at 19:45

Allain Lalonde

91,574
70
187
238

T-SQL to trim a datetime to the nearest date?

8 Answers8

Linked