MS SQL Date Only Without Time

Question

Question

Hello All,

I've had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL. Essentially, I want to take a DateTime value of say 2008-12-1 14:30:12 and make it 2008-12-1 00:00:00. Alot of the queries we run for reports use a date value in the WHERE clause, but I either have a start and end date value of a day and use a BETWEEN, or I find some other method.

Currently I'm using the following: WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam

However, this seems kinda clunky. I was hoping there would be something more simple like CAST([tstamp] AS DATE)

Some places online recommend using DATEPART() function, but then I end up with something like this:


WHERE DATEPART(year, [tstamp]) = DATEPART(year, @dateParam)
AND DATEPART(month, [tstamp]) = DATEPART(month, @dateParam)
AND DATEPART(day, [tstamp]) = DATEPART(day, @dateParam)

Maybe I'm being overly concerned with something small and if so please let me know. I just want to make sure the stuff I'm writing is as efficient as possible. I want to eliminate any weak links.

Any suggestions?

Thanks,
C

Solution

Thanks everyone for the great feedback. A lot of useful information. I'm going to change around our functions to eliminate the function on the left hand side of the operator. Although most of our date columns don't use indexes, it is probably still a better practice.

possible duplicate of [Most efficient way in SQL Server to get date from date+time?](http://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime) — Richard Szalay, May 28 '10 at 09:51

score 101 · Answer 1 · edited Jun 22 '12 at 19:46

101

If you're using SQL Server 2008 it has this built in now, see this in books online

CAST(GETDATE() AS date)

edited Jun 22 '12 at 19:46

mattmc3

17,595
7
83
103

answered Jan 21 '09 at 21:38

JoshBerke

66,142
25
126
164

1

Good to know! It's about time. :) – Lusid Jan 21 '09 at 21:43
1

No kidding:-) There's a lot new here with dates, looks like they have a timespan, more accurate datetime with customizable precision etc...of course they had to go and name the new date time...datetime2...ugh. – JoshBerke Jan 21 '09 at 21:53
Exactly what I needed. I ran through so many other convoluted solutions before finding yours. Thank you so much. +1 – Baxter Jan 20 '15 at 19:51

score 40 · Accepted Answer · answered Jan 21 '09 at 21:37

that is very bad for performance, take a look at Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

functions on the left side of the operator are bad

here is what you need to do

declare @d datetime
select @d =  '2008-12-1 14:30:12'

where tstamp >= dateadd(dd, datediff(dd, 0, @d)+0, 0)
and tstamp < dateadd(dd, datediff(dd, 0, @d)+1, 0)

Run this to see what it does

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

score 11 · Answer 3 · answered Jan 21 '09 at 21:47

11

The Date functions posted by others are the most correct way to handle this.

However, it's funny you mention the term "floor", because there's a little hack that will run somewhat faster:

CAST(FLOOR(CAST(@dateParam AS float)) AS DateTime)

answered Jan 21 '09 at 21:47

Joel Coehoorn

399,467
113
570
794

In my opinion converting to float is poor practice, because a round-trip conversion to datetime is not reliable. Please see [this post for more detail](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991). Casting to float is also not as fast as the DateDiff solution. – ErikE Sep 13 '10 at 00:35
1

@Emtucifor - this is an old post. Updated info is here: http://stackoverflow.com/questions/923295/how-to-truncate-a-datetime-in-sql-server/923322#923322 and here: http://stackoverflow.com/questions/1427469/compare-dates-in-t-sql-ignoring-the-time-part/1427507#1427507 – Joel Coehoorn Sep 13 '10 at 03:47
Thanks, Joel, I'll take a look at your links. I just wanted to update old posts so no one is misled. – ErikE Sep 13 '10 at 04:05
Okay... looks like I need to run my speed tests in SQL 2000. I'll try to do that soon. In the meantime, I'm still suspicious of this because round-trip conversions from datetime to float and back do not preserve the original value. And for the record, the fastest way (at least in SQL 2008, barring converts to the Date data type) is not this method but `Convert(datetime, Convert(int, @dateParam - 0.50000004))`. – ErikE Sep 13 '10 at 04:15

score 10 · Answer 4 · edited Jun 07 '11 at 12:12

10

CONVERT(date, GETDATE()) and CONVERT(time, GETDATE()) works in SQL Server 2008. I'm uncertain about 2005.

edited Jun 07 '11 at 12:12

DarthJDG

16,511
11
49
56

answered Nov 09 '09 at 18:35

AaronS

425
1
5
10

Those data types don't exist in SQL 2005. – ErikE Sep 13 '10 at 00:50

score 6 · Answer 5 · edited Jun 06 '11 at 21:00

6

How about this?

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

edited Jun 06 '11 at 21:00

answered Jun 06 '11 at 18:45

Sameer

61
1
1

score 1 · Answer 6 · edited Jun 07 '11 at 12:12

1

DATEADD(d, 0, DATEDIFF(d, 0, [tstamp]))

Edit: While this will remove the time portion of your datetime, it will also make the condition non SARGable. If that's important for this query, an indexed view or a between clause is more appropriate.

edited Jun 07 '11 at 12:12

DarthJDG

16,511
11
49
56

answered Jan 21 '09 at 21:35

Mark Brackett

84,552
17
108
152

score 1 · Answer 7 · answered Jan 21 '09 at 21:35

Yes, T-SQL can feel extremely primitive at times, and it is things like these that often times push me to doing a lot of my logic in my language of choice (such as C#).

However, when you absolutely need to do some of these things in SQL for performance reasons, then your best bet is to create functions to house these "algorithms."

Take a look at this article. He offers up quite a few handy SQL functions along these lines that I think will help you.

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Using a function on a column value is going to kill performance. Please update your answer to make it clear that a UDF should be used on a scalar date value and then the column compared to a date range of one day. — ErikE, Sep 13 '10 at 00:36

Sam Saffron · Answer 8 · 2009-01-21T22:05:56.523

1

Careful here, if you use anything a long the lines of WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam it will force a scan on the table and no indexes will be used for that portion.

A much cleaner way of doing this is defining a calculated column

create table #t (
    d datetime, 

    d2 as 
        cast (datepart(year,d) as varchar(4)) + '-' +
        right('0' + cast (datepart(month,d) as varchar(2)),2) + '-' + 
        right('0' + cast (datepart(day,d) as varchar(2)),2) 
) 
-- notice a lot of care need to be taken to ensure the format is comparable. (zero padding)

insert #t 
values (getdate())

create index idx on #t(d2)

select d2, count(d2) from #t 
where d2 between '2008-01-01' and '2009-01-22'
group by d2
-- index seek is used

This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions.

edited Jan 21 '09 at 22:05

answered Jan 21 '09 at 21:53

Sam Saffron

128,308
78
326
506

Sam, creating a calculated column with an index is total overkill. Put the index on d instead, and then use `WHERE d >= '20080101' AND d < '20090123'` and you'll get an index seek. See [this post for performance testing on the various methods to remove the time portion](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991). – ErikE Sep 13 '10 at 00:38
@Emtucifor this technique offers a seriously cool performance optimisations if you want to group on days. otherwise I agree SQLMenaces solution works just fine here. – Sam Saffron Sep 20 '10 at 06:47
Sam, if one has to group by the value, why turn it into a string (one that also happens to have superfluous dashes taking extra storage for no reason)? Use DateDiff() to do the job, which not only is faster (which I realize is less of an issue in this situation) but is much harder to get wrong, as your warning in the code seems to indicate. – ErikE Sep 20 '10 at 16:10

score 0 · Answer 9 · answered Nov 19 '09 at 15:59

0

Alternatively you could use

declare @d datetimeselect
@d =  '2008-12-1 14:30:12'
where tstamp 
  BETWEEN dateadd(dd, datediff(dd, 0, @d)+0, 0) 
  AND dateadd(dd, datediff(dd, 0, @d)+1, 0)

answered Nov 19 '09 at 15:59

Xander

21
2

This answer is incorrect because BETWEEN uses inclusive end points, and the query will wrongly include tstamp values of '20081202 00:00:00.000'. – ErikE Sep 13 '10 at 00:49

Rob Boek · Answer 10 · 2011-01-05T00:31:29.720

0

Here's a query that will return all results within a range of days.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = DATEADD(day, -30, GETDATE())
SET @endDate = GETDATE()

SELECT *
FROM table
WHERE dateColumn >= DATEADD(day, DATEDIFF(day, 0, @startDate), 0)
  AND dateColumn <  DATEADD(day, 1, DATEDIFF(day, 0, @endDate))

edited Jan 05 '11 at 00:31

answered Jan 21 '09 at 21:35

Rob Boek

1,953
16
20

using the -2 MS trick is poor practice, because it relies on the resolution of the datetime data type. People have [ended up in unpleasant situations because of this](http://stackoverflow.com/questions/3584850/sql-server-datetime-parameter-rounding-warning/3589018#3589018). Instead, stop using BETWEEN and use >= and <. – ErikE Sep 13 '10 at 00:46

score 0 · Answer 11 · edited Jun 07 '11 at 12:13

0

FWIW, I've been doing the same thing as you for years

CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam

Seems to me like this is one of the better ways to strip off time in terms of flexibility, speed and readabily. (sorry). Some UDF functions as suggested can be useful, but UDFs can be slow with larger result sets.

edited Jun 07 '11 at 12:13

DarthJDG

16,511
11
49
56

answered Jan 21 '09 at 21:48

Booji Boy

4,522
4
40
45

The problem with this is that it can't use an index on the [tstamp] column. – Rob Boek Jan 21 '09 at 21:53
The other problem is that string manipulation for SQL dates is a common antipattern. http://stackoverflow.com/questions/346659/what-are-the-most-common-sql-anti-patterns#346679 – Amy B Jan 21 '09 at 21:59
Converting to varchar is also slower. See [this post for more detail](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991). – ErikE Sep 13 '10 at 00:46

score -1 · Answer 12 · edited Jun 07 '11 at 12:14

-1

WHERE DATEDIFF(day, tstamp, @dateParam) = 0

This should get you there if you don't care about time.

This is to answer the meta question of comparing the dates of two values when you don't care about the time.

edited Jun 07 '11 at 12:14

DarthJDG

16,511
11
49
56

answered Jan 21 '09 at 21:39

Benjamin Autin

4,143
26
34

The problem with this is that it can't use an index. – Rob Boek Jan 21 '09 at 21:53
This #1 can't use an index as @Rob said and #2 even without an index forces a calculation on every row in the table instead of just once when the expression is on the right side as in SQLMenace's best-practice solution. – ErikE Sep 13 '10 at 00:47

MS SQL Date Only Without Time

Question

Solution

12 Answers12

Linked