Removing Time From A Datetime Value

How much time would a DateTime have if a DateTime had no time?

Ok, forgive the bad pun. But the point remains is a valid one. How can we efficiently strip off the time portion of a DateTime value in SQL Server?

I’ve seen a several methods proffered on the Internet. Some are quite clever; others are not so clever. The latter includes converting the DateTime to a VarChar and using string manipulation techniques to loop off the time. That’s not going to perform well.

So what is the best way? Let’s find out.

Three Methods

I’m going to narrow down the field to only three methods for manageability. There may be other methods out there. If you know of one that’s not covered here and you think may be better, please let me know.

To test each case, I’ll employ the conversion 10,000,000 times to see which finishes the quickest. I’ll do this with the following code.

SET NOCOUNT ON
DECLARE @cnt INT = 1;
DECLARE @start DATETIME = GETDATE();
DECLARE @var DATETIME;

WHILE @cnt < 10000000
BEGIN
SET @var = [time removal option]
SET @cnt += 1;
END

SELECT @var;
PRINT CAST(DATEDIFF(millisecond, @start, GETDATE()) AS INT);

Cast To A Float And Back

First let’s look at a technique that casts the DateTime to a float, gets the floor of the resulting value, and then casts it back as a DateTime.

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

This option finished in a 16,256 milliseconds.

Adding A Date

The next option uses the DATEDIFF function to calculate the number of days since the beginning of time and then uses the DATEADD function to convert it back to a DateTime value.

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

This completed in 15,593 milliseconds, slightly better then the first option.

Converting To A Char And Back

Finally, let’s consider a technique that converts the DateTime to a Char of a specified format and then casts it back to a DateTime.

CAST(CONVERT(CHAR(11), GETDATE(), 113) AS DATETIME

As you may have expected, the conversion to a Char significantly slowed the process down. In fact coming it at 32,698 milliseconds, it took over twice as long as the second method.

Say, Do You Have The Time?

From the three methods I’ve considered, the second method which included using a DATEDIFF to count the number of days since the beginning of time slightly edged out the first method. Both significantly beat converting the DateTime to a Char string.

So, do you have a favorite method? If so, use the test script I’ve posted about and see how it performs. Let me know how it does.

Advertisements

24 Responses to Removing Time From A Datetime Value

  1. Awesome post title!

    I’ve been a fan of the DATEDIFF() method for a long time (and I suggest you try your loop with just DATEDIFF(), you don’t need the DATEADD() as long as you stick with pre-2008 datetime types). I came to that decision based on a very similar approach to yours – try various methods in very large loops, and see which comes back fastest. Difference between casting to float/int and DATEDIFF() is minimal, but would you rather have $1 million or $1.0001 million? 🙂

  2. Joe says:

    By the way, if you need to calculate the end of the day you can do it by

    SELECT @end_date = DATEADD(MINUTE, -1, DATEADD(D, 1, CAST(FLOOR(CAST(@end_date AS float)) AS datetime)));

    This uses the first method described above.

  3. Never found much value in calculating the end of the day. I assume you are using this for BETWEEN @start_date AND @end_date? I would much rather use >= @start_date AND < DATEADD(DAY, 1, @start_date). This is compatible with both DATETIME and SMALLDATETIME. With your method, if the column is DATETIME, you have the potential of missing rows that occurred in the last minute. And it's less tidy; I'm all about tidy. 🙂

  4. Daniel Kim says:

    am i missing something, or is this for T-SQL before 2008? not sure how this performs but this seems simpler:

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

  5. Andrew says:

    Cast(@datetime as date)

    By far the easiest and fastest – plus SQL will still use indexes on datetime columns – it won’t if you use datediff

    • Joe Webb says:

      Thanks Andrew. Yes, columns wrapped up in a function typically cannot use index seeks.

      For the post I was considering T-SQL code for systems that do not have the Date datatype. Thanks for mentioning it.

    • Andrew, the code we’re talking about is typically when we’re checking if column is in some range. The datediff operations in these cases are almost always on variables or constants, not on columns, and won’t affect the usability of an index. I’ve shared some thoughts on date range queries in my “bad habits” series and one of the things I stress is to avoid performing these operations on the columns (e.g. WHERE CONVERT(DATE, column) = CONVERT(DATE, CURRENT_TIMESTAMP)):

      http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

      Just as a quick example, these will both use an index on column just fine:

      WHERE column >= CONVERT(DATE, CURRENT_TIMESTAMP)
      AND column = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP
      AND column < DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))

      However, the former won't work for you if you need to worry about deploying your code to SQL Server 2005, because it doesn't support the DATE data type.

  6. Pingback: Tweets that mention Removing Time From A Datetime Value « WebbTech Solutions -- Topsy.com

  7. Johan Bijnens - ALZDBA says:

    SSC member Lynn Pettis posted a little overview at http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

    It didn’t have the nice performance numbers, but is still a good ref.

    Thank you for these tests.

  8. Marc Brooks says:

    I’ve got a ton about practical uses of the DATEADD(DATEDIFF trick and proper date/time range comparisons here:

    http://bit.ly/71dvRP

  9. Joe Celko says:

    Hopefully T-SQL will get the CURRENT_DATE to go with the CURRENT_TIMESTAMP and we will not have to do implicit or explicit CAST ( CURRENT_TIMESTAMP AS DATE) in code.

    • Joe, why? We need more ways to say this?

      DECLARE @d DATE = SYSDATETIME();
      SELECT @d;
      GO
      DECLARE @d DATE = GETDATE();
      SELECT @d;
      GO
      DECLARE @d DATE = CURRENT_TIMESTAMP;
      SELECT @d;

      If it’s going to be used in a DATE data type, I don’t see why we need yet another system function that does what several already do.

      I can see maybe if you want to store a date without time in a SMALLDATETIME or DATETIME column, but why not just change it to DATE? If you are only going to chop the time off in *some* cases, then your method will require two different paths, one where CURRENT_DATE is used, and one where CURRENT_TIMESTAMP is used.

    • Joe Webb says:

      The Date datatype was a great addition to SQL, but I’m not sure that I see a lot of value in a CURRENT_DATE function. What use case are thinking about when you wish for it?

      • My guess is he just wants to say:

        SELECT CURRENT_DATE;

        And that comes back as a DATE type without having to perform any convert etc. Though I find very little value in that. For most use cases, if the destination (variable, column, etc.) is a DATE type, then the conversion is done for you.

        A slightly more useful use case:

        SELECT * FROM table WHERE date_column = CURRENT_DATE;

        If you use SYSGETDATE() or CURRENT_TIMESTAMP or SYSDATETIME(), obviously you’re not going to come up with the any rows. Another one that is bound to trip people up, because there is no implicit conversion (though I think there should be):

        DECLARE @d DATE = SYSDATETIME();
        IF @d = SYSDATETIME()
        BEGIN
        PRINT ‘they are the same’;
        END

        I see Celko’s point to a small extent, but I think 99% of the SQL Server world is already well aware of the limitations that our assumptions about date/time data places on us. 🙂

  10. Pingback: SQL Server Blogs and news for the week 21/01/11 | John Sansom - SQL Server DBA in the UK

  11. Pingback: A NULL Is Not Unknown « WebbTech Solutions

  12. Pingback: Writing Better Queries Presentation Materials « WebbTech Solutions

  13. Pingback: TSQL Tip O’ the Week – 8/16/11 – DateDiff « SQL Feather and Quill

  14. Manoj Bhatt says:

    This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it by visiting this url.

    http://mindstick.com/Articles/b08c8553-60a2-4f35-a58f-60c538c7d136/?%E2%80%98SELECT%E2%80%99%20Command%20with%20Date%20Method

    Its also helped me lot to complete my task.
    Thanks

  15. CalvinSwods says:

    Hot girls looking for sex in your city: https://hec.su/jUDR?tEhsGvfA2Ld4

    Popular tags: weekend dating in singapore, speed dating psychology, beste gratis dating sites, are jeevika and viren dating in real life, esl matchmaking, victorian dating laws, lee dong wook lee da hae dating rumor, gay hookup websites australia, cyrano dating agency ending, meaning of random hookup, dating spots in kathmandu, harmony dating russian, hook up pomona, speed dating form, asian dating sites cupid, need dating help, top city for interracial dating, magnetic field reversal dating, discount codes uniform dating, millionaires dating sites in usa, speed dating alternatives, good profile headline for dating sites, sample online dating headlines, dating a nurse t shirt, how to tell if a girl likes you through online dating, d/s dating sites, dating without makeup, raw food dating website, online dating sites in the usa, sugar daddy uk online dating, dating agency in singapore, top 50 dating sites, speed dating 45-55 ans paris, portland speed dating events, dating ocd woman, dating agency north west, soldier dating scams, dating online top 10, speed dating today, song ji hyo and song joong ki dating, 100 free dating sites in netherland, nyc dating service, gay dating site in canada, gay dating website nyc, military dating online, female sociopath dating, hook up points, thrill dating app india, can carbon dating be used on stone, christian dating meeting parents, luna and chen dating rumor, free dating in glasgow, long distance relationship dating questions, hook up charter phone, popular online dating sites canada, speed dating kaunas, what to expect when dating a cuban man, all fish in the sea dating, free dating sites in mumbai, worcester dating sites uk, hero academy matchmaking, new zealand free dating service, sampark online dating, russian dating sites buzzfeed, beli buku i kissed dating goodbye, dating service kostenlos, older dating nz, dealing with your ex dating someone else, internet dating funny, dating categories, uk disabled dating sites, dating pisces woman, dating tips for male virgins, phoenix hookup sites, dating site profile writer, dating for over forty, gay dating difficulties, online dating chat no registration, dota 2 matchmaking report, once dating app reddit, online dating restaurant, dating on earth dbsk, tall person dating short person, the hookup security square mall, dating blog toronto, gps hookup app android, pof com dating site, free dating inverness, manchester evening news dating, paddy dating show, dating rs prussia, singers dating football players, dating dna love cell ep 1 eng sub, dating app london, dating 30 years ago, jewish dating sites in israel, dating website about me template, free uk online dating websites, define officially dating, disability dating sites australia, hook up dimmer switch, interracial dating central uk, hookup culture high school, dating moments tumblr, best dating websites 2015 uk, motorhome dating, catholic girl dating jewish boy, romantic dating places in kathmandu, totally free lds dating sites, dating a girl with borderline, foreign dating site free, dl dating sites, masterchef brent and laura dating, free examples of online dating profiles, korean celebrity dating 2016, 100 free dating ireland, speed dating midlothian, how does aram matchmaking work, why does radioactive dating only work with igneous rocks, defiance matchmaking, free templates for dating site, did chanel west coast dating ryan sheckler, dads against dating daughters shirt, dating speed dating, is iyanya dating yvone nelson, dating a man with add, search all online dating sites, good emails for dating sites, pof dating site app, macleans online dating marriage, online dating greeting examples, positive of online dating, hookup colorado springs, totally free mobile phone dating, how to answer why are you on a dating site, dating signals, match dating site promo code, dating until something better comes along, 10 things dating someone with anxiety, kid online dating sites, vintage dating london, cycle dating, is christian mingle a hookup site, disadvantages of dating a younger man, christian dating sites in thailand, differences between dating boy man, hookup mobile, advice for single mothers dating, dating recently separated woman, hookup guard website, free dating services in bangalore, virtual online dating, free online dating fish in the sea, parody dating profile, roblox dating place, speed dating fort collins co, online dating sites free in usa, qatar dating sites free, jordan shoes hookup, the village church christian dating, madrid dating site, assam dating site, houston area hook up, online dirty dating games, bexley dating, dating wants to take it slow, are leonard and penny dating again, dating sites in kolkata, houston dating site, lexington dating services, online games simulation dating, free asian dating app, best 100 free black dating sites, free online dating no hidden charges, dating agencies in moldova, free asia dating sites, muslim dating london, graphics card hook up, dating free uk, how to find cheating husband on dating sites, girl dating sims online, sample dating website message, friends and dating website, free dating site for new zealand, why is online dating so weird, dating a year older girl, dating amanco engines, dating and kissing games, how about we dating promo code, arab dating sites canada, free online dating sites that actually work, legal dating age in massachusetts, pretty girl dating fat guy, ghana online dating site, dating an entp personality type, watch dating alone eng sub, just dating birthday gifts, widowed parent dating again, hook up bra, best dating app for delhi, indonesian dating sites free, dating male aquarius, dating website what to write in profile, pretty little liars actors dating, what should happen after two months of dating, create your own dating site, usp chapter 797 beyond use dating, speed dating cafe, dating sites for soldiers in uk, rich sugar daddy dating site, wot hummel matchmaking, free lesbian online dating websites, chinese zodiac dating sites, azubi speed dating dГјsseldorf unternehmen, ultrasound pregnancy dating scan, asian dating websites, online dating south africa bloemfontein, improved matchmaking algorithm for semantic web services based on bipartite graph matching, download dating nach, carbon dating angels, disabled dating websites uk, dating advanced search, unexplainable russian dating site photos, speed dating today, isfj dating istj, nummerplaat dating, legal dating age difference florida, 5 non negotiables dating, dating a girl that looks like your ex, speed dating hattiesburg ms, help writing my online dating profile, matchmaking max payne 3, the best online dating sites, dating before deployment, good free dating websites, ghana popular dating sites, free download marriage without dating sub indo, online dating twin cities, chemistry dating search, dating advice for introverted guys, gay dating sites in norway, african dating kissesofafrica, best private dating services, harry and daphne dating fanfiction, dating 25, watch duggar dating rules, why do straight guys hook up with gay guys, long phone calls dating, creepy online dating messages, temporary matchmaking cooldown cs go, free dating sites for farmers, dating site aust, black muslim dating uk

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s