When taking the date only from datetime - Which is best and why?

Question

So there's some disagreement at my work recently about which is the best way to take the month from getdate() (or alternatively from a datetime column).

There are two ways I know of that are fairly standard for doing this:

Method #1:

select DateAdd(Day, 0, DateDiff(Day, 0, Getdate()))

And Method #2:

select Cast(Floor(Cast(Getdate() as Float)) as Datetime)

I'm a big fan of method #2 as I find it much easier to internalize as well as being (slightly) faster. However, I've been told that method #1 is the standard way of doing this. My question is why? Which of these two would I be best served having as the method for taking a month from a date in my arsenal and why?

Note you are taking the day and not the month. The second one is slightly faster, but if you change the syntax to: -- DateAdd(Day, DateDiff(Day, 0, Getdate()), 0) -- then the first syntax seems to run even faster than the second. However -- Cast(Getdate() as date) -- wins. But not as much as i expected — t-clausen.dk, Jun 07 '12 at 08:45
possible duplicate of [What's the BEST way to remove the time portion of a datetime value (SQL Server)?](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server) — Martin Smith, Jun 11 '12 at 13:17

score 4 · Answer 1 · answered Jun 07 '12 at 03:36

In SQL Server 2008, I'm just as happy with:

SELECT CONVERT(DATE, CURRENT_TIMESTAMP);

In addition to being much simpler and self-documenting, this is one exception where the conversion can be applied to a column and still utilize the index. For example which is easier to read, if both yield roughly the same performance?

WHERE CONVERT(DATE, col) = CONVERT(DATE, CURRENT_TIMESTAMP)

Vs.

WHERE col >= CONVERT(DATE, CURRENT_TIMESTAMP) -- or any of your alternatives
AND col < DATEADD(DAY, 1, CONVERT(DATE, CURRENT_TIMESTAMP))

Of these three methods, you might find minuscule little performance differences, but testing them on their own is of little value IMHO. How often are you stripping time from a date a million times in isolation? You should find that given the same semantics, as part of a real query, the cost of using DATEDIFF or FLOOR or CONVERT is not going to factor in.

As an aside, if you are assigning the result to an explicit DATETIME or SMALLDATETIME variable/column, or implicitly doing so via a where clause, you can skip the DATEADD part.

SELECT CONVERT(DATETIME, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

I believe the former is better as it involves no floating point math and just needs to access the first integer in the internal storage of a datetime. See this as well

edited May 23 '17 at 12:12

Community

1
1

answered Jun 07 '12 at 03:04

Charles Bretana

143,358
22
150
216

What benefits are there to this? From what I've found Method #2 ends up being faster. – Michael A Jun 07 '12 at 03:06

score 2 · Answer 3 · answered Jun 07 '12 at 03:21

I ran scripts to test and your method #1 and method #2 were both very similar in speeds. The posted answer by Charles Bretana was actually slower in every test. Granted, these are fairly unscientific, so test them out yourself. Here's the script I wrote:

declare @starttime datetime
declare @endtime datetime

set @starttime = GETDATE()
print convert(varchar(30), @starttime, 109)

declare @cnt int
set @cnt = 0

declare @datevar datetime

while @cnt < 10000000
begin

    select @datevar = DateAdd(Day, 0, DateDiff(Day, 0, Getdate())) -- Replace this with whichever function you'd like to test
        , @cnt = @cnt + 1
end

set @endtime = GETDATE()
print convert(varchar(30), @endtime, 109)

print cast(datediff(ms, @starttime, @endtime) as varchar(20)) + ' ms'

Just replace the select @datevar = line with the appropriate ones. On my dev machine methods 1 and 2 usually ran in 7200 - 7300 ms. The method posted by Charles usually averaged 8300 - 8500 ms

Guess my post doesn't make as much sense now because Charles edited his. Either way, it's still useful to show how easy it is to test out different methods. — Levi W, Jun 07 '12 at 03:25
I know I personally still benefited from this on an educational level. Definitely worth retaining. Thank-you! — Michael A, Jun 07 '12 at 03:41

score 1 · Answer 4 · answered Jun 07 '12 at 10:23

1

To take the month from getdate():

SELECT DATEPART(MONTH, GETDATE())

answered Jun 07 '12 at 10:23

Bliek

466
3
7

I'm curios why people don't use this for smaller queries given it's syntactically simple... Any thoughts? – Michael A Jun 07 '12 at 10:36

When taking the date only from datetime - Which is best and why?

4 Answers4