By Stephen Perelson


2009-07-24 12:49:54 8 Comments

Which method provides the best performance when removing the time portion from a datetime field in SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

or

b) select cast(convert(char(11), getdate(), 113) as datetime)

The second method does send a few more bytes either way but that might not be as important as the speed of the conversion.

Both also appear to be very fast, but there might be a difference in speed when dealing with hundreds-of-thousands or more rows?

Also, is it possible that there are even better methods to get rid of the time portion of a datetime in SQL?

23 comments

@emehex 2017-01-10 02:06:03

I really like:

[date] = CONVERT(VARCHAR(10), GETDATE(), 120)

The 120 format code will coerce the date into the ISO 8601 standard:

'YYYY-MM-DD' or '2017-01-09'

Super easy to use in dplyr (R) and pandas (Python)!

@Alan 2014-09-24 12:42:20

Just in case anyone is looking in here for a Sybase version since several of the versions above didn't work

CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)
  • Tested in I SQL v11 running on Adaptive Server 15.7

@EWit 2014-09-24 13:03:32

This is better fit as an edit on the accepted answer. With 20 other answers this will be buried and nigh unfindable. Also the accepted answer makes mention of using cast: For SQL Server 2008+, you can CAST to date. Or just use date so no time to remove.

@Andriy M 2014-09-25 05:36:19

It would be best to post this as an answer to an equivalent Sybase question. If there is no such question, you are free to create one (and answer it yourself).

@Andriy M 2014-09-25 05:40:49

Besides, it is pointless to specify a third parameter to CONVERT when you are converting a datetime to date: neither of those has an inherent format.

@Max Vargas 2015-06-23 19:20:43

Here I made a function to remove some parts of a datetime for SQL Server. Usage:

  • First param is the datetime to be stripped off.
  • Second param is a char:
    • s: rounds to seconds; removes milliseconds
    • m: rounds to minutes; removes seconds and milliseconds
    • h: rounds to hours; removes minutes, seconds and milliseconds.
    • d: rounds to days; removes hours, minutes, seconds and milliseconds.
  • Returns the new datetime

create function dbo.uf_RoundDateTime(@dt as datetime, @part as char) returns datetime as begin if CHARINDEX( @part, 'smhd',0) = 0 return @dt; return cast( Case @part when 's' then convert(varchar(19), @dt, 126) when 'm' then convert(varchar(17), @dt, 126) + '00' when 'h' then convert(varchar(14), @dt, 126) + '00:00' when 'd' then convert(varchar(14), @dt, 112) end as datetime ) end

@Andriy M 2015-06-25 19:32:04

@Max Vargas 2015-06-26 20:53:29

Thanks Andriy! I didn't know my recommendation wasn't that efficient. At least it works, but you are right.

@Jamie G 2013-08-29 21:30:45

I think that if you stick strictly with TSQL that this is the fastest way to truncate the time:

 select convert(datetime,convert(int,convert(float,[Modified])))

I found this truncation method to be about 5% faster than the DateAdd method. And this can be easily modified to round to the nearest day like this:

select convert(datetime,ROUND(convert(float,[Modified]),0))

@ErikE 2013-10-29 04:31:35

Converting to float is not safe.

@Anto Raja Prakash 2013-10-23 12:33:54

In SQL Server 2008, you can use:

CONVERT(DATE, getdate(), 101)

@Andriy M 2013-10-26 19:46:24

The third argument has absolutely no bearing on the result when converting from a datetime to a date, and so your solution effectively boils down to just CONVERT(DATE,getdate()), which has already been suggested more than once.

@Arjan Fraaij 2012-05-04 15:00:40

Of-course this is an old thread but to make it complete.

From SQL 2008 you can use DATE datatype so you can simply do:

SELECT CONVERT(DATE,GETDATE())

@Andriy M 2012-05-04 17:13:05

The accepted answer mentions this option.

@gbn 2009-07-24 13:09:38

Strictly, method a is the least resource intensive:

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

Proven less CPU intensive for same total duration a million rows by some one with way too much time on their hands: Most efficient way in SQL Server to get date from date+time?

I saw a similar test elsewhere with similar results too.

I prefer the DATEADD/DATEDIFF because:

Edit, Oct 2011

For SQL Server 2008+, you can CAST to date. Or just use date so no time to remove.

Edit, Jan 2012

A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server

Edit, May 2012

Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but generally it will be a bad idea ...

@Choco Smith 2014-12-15 08:51:06

@David Sopko for the Oct 2011 edit then code would be: select cast(GETDATE() as date)

@ozkary 2017-01-19 23:28:00

For more recent versions of SQL, using date instead of datetime avoids the need to deal with hours. Use the following sample: declare noTime date = getdate(), withTime datetime = getdate() select @noTime,@withTime

@Xedni 2017-07-11 17:02:44

the cast as date is great if you just need the date. However often you need the current date at midnight so you can then do some further date manipulation. the DATE data time is obnoxiously restrictive at what it will let you do with regard to things like dateadd, datediff and interacting with other date/time data types. For those cases, the DATEADD() approach reigns king.

@Metaphor 2014-04-03 22:54:13

In SQL Server 2008, there is a DATE datetype (also a TIME datatype).

CAST(GetDate() as DATE)

or

declare @Dt as DATE = GetDate()

@joelmdev 2018-01-29 18:16:27

This is what I used and it worked well. Seems like the simplest answer. Any downsides over using in conjunction w/ CONVERT?

@Dr. Drew 2014-03-24 08:52:41

How about select cast(cast my_datetime_field as date) as datetime)? This results in the same date, with the time set to 00:00, but avoids any conversion to text and also avoids any explicit numeric rounding.

@Dr. Drew 2014-03-25 09:22:14

They are not the same. The other answers suggested casting it to a date with no time component and leave it like that. My posting sets it to a datetime with the time at midnight. There is a big difference; try exporting to MS Excel and you'll see that it handles the datetime much better than date.

@Mikael Eriksson 2014-03-25 09:38:07

The first one is exactly the same.

@Dr. Drew 2014-03-25 11:07:31

Ok, yes, I do see that one now. I will be happy to remove my answer as a duplicate, if necessary.

@Diego 2014-02-10 14:11:33

select CONVERT(char(10), GetDate(),126)

@Andriy M 2014-03-24 15:35:28

What is the principal difference of your suggestion from the method mentioned in @broslav's answer or from the method that was determined as slowest in this thread (same link as in the accepted answer)?

@broslav 2014-01-20 14:28:08

BEWARE!

Method a) and b) does NOT always have the same output!

select DATEADD(dd, DATEDIFF(dd, 0, '2013-12-31 23:59:59.999'), 0)

Output: 2014-01-01 00:00:00.000

select cast(convert(char(11), '2013-12-31 23:59:59.999', 113) as datetime)

Output: 2013-12-31 00:00:00.000

(Tested on MS SQL Server 2005 and 2008 R2)

EDIT: According to Adam's comment, this cannot happen if you read the date value from the table, but it can happen if you provide your date value as a literal (example: as a parameter of a stored procedure called via ADO.NET).

@Adam Wenger 2014-01-20 14:40:21

.999 cannot be stored in SQL Server in a DATETIME column. The highest available is .997 From: msdn.microsoft.com/en-us/library/ms187819.aspx you'll see that the values are rounded to have the thousandth place to 0, 3, or 7. The OP will not see the value from your test in their tables.

@broslav 2014-01-22 13:42:24

You are correct. I didn't mean to post this as an answer to the OP question, but as a comment for others to see, but I only had 11 reputation points and 15 is needed for commenting.

@Andriy M 2014-01-28 16:11:04

In your first snippet the string constant is implicitly converted to a datetime, in your second one it remains a string (and the 113 is just ignored).

@Jabu 2013-12-19 12:14:40

I would use:

CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + '/' CAST(MM(DATEFIELD) as varchar(2)) + '/' CAST(DD(DATEFIELD) as varchar(2)) as datetime
) 

Thus effectively creating a new field from the date field you already have.

@Andriy M 2013-12-26 09:46:28

Why would you do that? Do you think that extracting bits from a datetime value, converting them to strings, concatenating those together and finally converting the result back to datetime is better than e.g. performing direct calculations on the original datetime (the DATEADD/DATEDIFF method)?

@Andriy M 2013-12-26 09:46:57

Also, what are MM and DD? There are no such functions in SQL Server.

@CraigTP 2009-07-24 13:08:19

I, personally, almost always use User Defined functions for this if dealing with SQL Server 2005 (or lower version), however, it should be noted that there are specific drawbacks to using UDF's, especially if applying them to WHERE clauses (see below and the comments on this answer for further details). If using SQL Server 2008 (or higher) - see below.

In fact, for most databases that I create, I add these UDF's in right near the start since I know there's a 99% chance I'm going to need them sooner or later.

I create one for "date only" & "time only" (although the "date only" one is by far the most used of the two).

Here's some links to a variety of date-related UDF's:

Essential SQL Server Date, Time and DateTime Functions
Get Date Only Function

That last link shows no less than 3 different ways to getting the date only part of a datetime field and mentions some pros and cons of each approach.

If using a UDF, it should be noted that you should try to avoid using the UDF as part of a WHERE clause in a query as this will greatly hinder performance of the query. The main reason for this is that using a UDF in a WHERE clause renders that clause as non-sargable, which means that SQL Server can no longer use an index with that clause in order to improve the speed of query execution. With reference to my own usage of UDF's, I'll frequently use the "raw" date column within the WHERE clause, but apply the UDF to the SELECTed column. In this way, the UDF is only applied to the filtered result-set and not every row of the table as part of the filter.

Of course, the absolute best approach for this is to use SQL Server 2008 (or higher) and separate out your dates and times, as the SQL Server database engine is then natively providing the individual date and time components, and can efficiently query these independently without the need for a UDF or other mechanism to extract either the date or time part from a composite datetime type.

@ErikE 2013-10-29 04:30:39

Using a UDF can be good in some situations (like when scrubbing parameters). But in most situations it is an awful solution--running a UDF once for each row is a way to just kill the performance of a query, without any need for it!

@CraigTP 2013-10-29 07:12:07

@ErikE - I don't disagree, Erik, UDF's are performance killers which is why I say that, if you can use SQL Server 2008 or above and use a built-in datatype that does this for you, that will be the best solution (both in terms of achieving what's required and in terms of performance). If you're stuck with an older version of SQL Server that doesn't natively support this, you're going to give up something in order to achieve your requirements.

@ErikE 2013-10-29 18:12:45

True. It would be nice if the database engine gave us something that was SARGable, but easier to express. In the meantime, if you're looking for a value that's any time during a whole day, this is still the best solution (for at least older versions of SQL): WHERE DateColumn >= {TimeTruncatingExpression}(@DateValue) AND DateColumn < {TimeTruncatingExpression}(@DateValue + 1). I felt like I had to say something since you said "I almost always use UDFs" didn't explain any of the drawbacks, nor the way to make a date-only query SARGable.

@CraigTP 2013-10-30 09:16:20

@ErikE - No worries, Erik. When I've used UDF's, I've either been working with small data sets where performance isn't paramount, or more likely I've been filtering the query against the "raw" date field (to ensure sargability) but selecting the column with the UDF applied. As these are usually small datasets once filtered, running the UDF over this small number of records isn't such a performance hit. That said, you do raise a very good point and I've updated my answer to reflect this.

@Byju 2013-07-03 13:38:56

SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

@Andriy M 2013-07-04 11:51:18

A valid option, yes. Suggested more than once in this thread, though.

@user1920017 2012-12-20 20:56:08

For me the code below is always a winner:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));

@Andriy M 2012-12-21 18:12:49

Essentially same as @Gary McGill's suggestion.

@ErikE 2013-10-29 04:29:44

Casting as float is not safe.

@Aaron West 2012-02-06 19:05:12

I think you mean cast(floor(cast(getdate()as float))as datetime)

real is only 32-bits, and could lose some information

This is fastest cast(cast(getdate()+x-0.5 as int)as datetime)

...though only about 10% faster(about 0.49 microseconds CPU vs. 0.58)

This was recommended, and takes the same time in my test just now: DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

In SQL 2008, the SQL CLR function is about 5 times faster than using a SQL function would be, at 1.35 microseconds versus 6.5 microsections, indicating much lower function-call overhead for a SQL CLR function versus a simple SQL UDF.

In SQL 2005, the SQL CLR function is 16 times faster, per my testing, versus this slow function:

create function dateonly (  @dt datetime )
returns datetime
as
begin
return cast(floor(cast(@dt as float))as int)
end

@shantanu singh chauhan 2011-11-18 12:57:26

CAST(round(cast(getdate()as real),0,1) AS datetime)

This method does not use string function. Date is basically a real datatype with digits before decimal are fraction of a day.

this I guess will be faster than a lot.

@ErikE 2013-10-29 04:28:57

Casting as float is not safe.

@tjeuten 2011-10-13 14:40:19

If possible, for special things like this, I like to use CLR functions.

In this case:

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime DateOnly(SqlDateTime input)
    {
        if (!input.IsNull)
        {
            SqlDateTime dt = new SqlDateTime(input.Value.Year, input.Value.Month, input.Value.Day, 0, 0, 0);

            return dt;
        }
        else
            return SqlDateTime.Null;
    }

@Carter Cole 2010-07-09 16:06:45

Already answered but ill throw this out there too... this suposedly also preforms well but it works by throwing away the decimal (which stores time) from the float and returning only whole part (which is date)

 CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)

second time I found this solution... i grabbed this code off

@ErikE 2013-10-29 04:35:14

Converting to float is not safe.

@Jeff Meatball Yang 2009-07-24 14:20:17

Here's yet another answer, from another duplicate question:

SELECT CAST(CAST(getutcdate() - 0.50000004 AS int) AS datetime) 

This magic number method performs slightly faster than the DATEADD method. (It looks like ~10%)

The CPU Time on several rounds of a million records:

DATEADD   MAGIC FLOAT
500       453
453       360
375       375
406       360

But note that these numbers are possibly irrelevant because they are already VERY fast. Unless I had record sets of 100,000 or more, I couldn't even get the CPU Time to read above zero.

Considering the fact that DateAdd is meant for this purpose and is more robust, I'd say use DateAdd.

@usr 2012-06-03 18:58:52

That is horrible. I'd never put my data at risk like this. Who knows if this is correct for all datetimes, not just the ones you tested.

@ErikE 2013-10-29 04:28:39

@usr Oh, it's correct, it's just a magic number and shouldn't be used for that reason. If you want to check its correctness, just stuff all the possible dates for one day in a table and check the results! Also see this post for more information.

@usr 2013-10-29 07:12:25

@ErikE good point. Your answer provides the possibility of using '12:00:00.003' which I think is much better, though.

@Joel Coehoorn 2009-07-24 13:33:00

See this question:
How can I truncate a datetime in SQL Server?

Whatever you do, don't use the string method. That's about the worst way you could do it.

@Stephen Perelson 2009-07-24 13:48:23

Thanks, I figured this had to have been asked before. Strange though that my experiments pointed out that the float method is actually slower by 3.5% on SQL Server 2008 than the dateadd(dd,0, datediff(dd,0, getDate())) method. I did run my tests many times for each method and the database server was unused for anything else at the time.

@Joel Coehoorn 2009-07-24 14:44:31

Let's just say that I'm skeptical of benchmarks done by anyone who hasn't demonstrated that they do benchmarks regularly and in a very scientific way as part of their job. Even Thomas' benchmark in the link by gbn has some obvious problems when you look at it. That doesn't make it wrong necessarily, just not definitive. The cast/floor/cast method was the accepted fastest way for a very long time, and I suspect it was once indisputably true. That said, I am starting to reconsider it; especially for sql server 2008, where it's completely unnecessary anyway.

@Ben 2012-01-31 12:36:23

The string method is extremely easy to use, to read, and to remember. Those are very important factors which I think you are underestimating!

@Joel Coehoorn 2012-01-31 16:23:44

@Ben - easier to read than "CAST( x as Date)" ? The string method is also wrong because it doesn't always work. Deploy your database to a server with a different collation, and you're in big trouble.

@Ben 2012-01-31 18:58:18

@JoelCoehoorn, convert style 121 is called "ODBC Canonical". It does not vary with collation or locale. The string trick is also easy to generalise to year, year+month, day, hour or minute.

@ErikE 2013-10-29 04:27:36

@Ben The string trick teaches developers to use string conversions. They work, but date math is far, far superior, for many reasons, not the least of which is speed--but even more, for what learning to work with the dates-as-numbers confers on the developer and his mental abilities to be fluid with number manipulation in code.

@Gary McGill 2009-07-24 12:57:54

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

@MatBailie 2009-07-24 13:29:34

See GBN's answer, many have investigated this. DATETIMEs are NOT stored as floats, and so using DATEADD/DATEDIFF avoids the mathmatical manipulation need to CAST between types.

@Gary McGill 2009-07-24 14:16:54

I can accept that you might want to avoid a cast from DATETIME to FLOAT for the reason you describe, but in that case isn't the implicit conversion from zero in the OPs option (a) also a problem? Hmmm... I suppose in that case it's not a FLOAT and that the server is probably smart enough to discard the time info. OK, I concede :-)

@MatBailie 2009-07-26 15:03:20

The 0 is indeed an implicit conversion from a numeric type (INT I would guess) to a DATETIME. Because it's a constant expression, however, the optimiser can do that at compile time for Stored Procedures and only needs to do it once for dynamically execute SQL. In short, there is a one time overhead for that, the FLOAT based query has the equivilent overhead for every Row.

@usr 2012-06-03 18:57:47

Casting to float is terribly unprecise. This answer should be deleted. Nobody should use this code.

@ErikE 2013-10-29 04:25:07

Not to mention that it's not safe to cast to float and back to datetime--float doesn't have enough precision. Therefore I think it can't be recommended at all. See this post for more detail.

@Anton Gogolev 2009-07-24 12:55:14

Strip time on inserts/updates in the first place. As for on-the-fly conversion, nothing can beat a user-defined function maintanability-wise:

select date_only(dd)

The implementation of date_only can be anything you like - now it's abstracted away and calling code is much much cleaner.

@Philip Kelley 2009-07-24 14:21:48

I once devised a trigger to scrub times from selected columns. If the data can't be bad, you don't have to clean it.

@MatBailie 2009-07-29 11:55:45

There is a downside to the UDF approach, they're not SARGable. If used in JOINs or WHERE clauses, the optimiser can't use INDEXes to improve performance. Using the DATEADD/DATEDIFF approach, however, is SARGable and will be able to benefit from INDEXes. (Apparently the FLOAT method is SARGable too)

@ErikE 2013-10-29 04:34:15

@MatBailie I beg to differ! UDFs are definitely not SARGable, but neither is Dateadd nor is Convert to float! WHERE DateAdd(DateDiff(Column)) = @DateValue won't use an index. On the other hand, WHERE Column >= dbo.UDF(@DateValue) AND Column < dbo.UDF(@DateValue + 1) is SARGable. So be careful how you put it.

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

44 Answered Questions

36 Answered Questions

14 Answered Questions

[SOLVED] DateTime2 vs DateTime in SQL Server

29 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

22 Answered Questions

[SOLVED] Check if table exists in SQL Server

23 Answered Questions

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

36 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

30 Answered Questions

[SOLVED] Daylight saving time and time zone best practices

Sponsored Content