Home > SQL > Removing time from SQL datetime

Removing time from SQL datetime

Quick Date and time SQL

August 6th 2009 | Kevin Jones

Removing time from SQL datetime

We use SQL server to store data—a lot. Sometimes we need to remove the time for a DATETIME column. This is useful for analysis or grouping by day. There is no built-in function that does this, so developers often write their own. I’ve written several, some of which are much faster than others.

Some people convert it to a VARCHAR without the time, and back to a DATETIME (effectively making the time midnight). Something like this:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(15), GETDATE(), 101))

This works, but it’s slow when operating over tens of thousands of rows. It’s also a little tricky to remember if you don’t write SQL often.

If you use SQL 2008, you can use a new data type called DATE. It’s compatible with DATETIME, and it’s as simple as a CONVERT or CAST, meaning you can use operators on them.

SELECT CAST([Registered] AS DATE) FROM Users

You can cast it back to a DATETIME if you need the type to be a DATETIME. If your column doesn’t care about time and you use SQL 2008, I recommend changing the column type to DATE so you don’t have to worry about time.

However, if you use SQL 2005 or below, I recommend casting it to a FLOAT, calling FLOOR, then back to a DATETIME.

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

This is significantly faster than converting to the a VARCHAR and back to a DATETIME, mainly because when you cast a DATETIME to VARCHAR, SQL Server considers things like the collation, and what format it should use, then does it all again when parsing it back to a DATETIME.

When casting to a FLOAT, the integer part is the number of days, and the decimal part is the fraction part of the day, or time. Using FLOOR effectively gets rid of the decimal. You can also use CEILING to push it to the next day.

Kevin Jones is a Senior .NET Consultant and Team Lead at Thycotic Software, an agile software services and product development company based in Washington DC. Secret Server is our flagship password management software product.

  1. August 7, 2009 at 5:41 pm

    I believe keeping it as datetime is even faster. I remember doing performance tests on huge loops but I don’t recall if I ever published the results.

    SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));

    Of course if you are assigning the value to a column or a DATETIME/SMALLDATETIME variable, you can avoid one operation:

    DECLARE @d SMALLDATETIME;
    SET @d = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
    SELECT @d;

  2. August 9, 2009 at 10:34 am

    Hi Aaron,

    Thanks for the excellent tip! However, I did a little bit of performance testing myself and found the cast to FLOAT is just ever so slightly faster — most of the time 🙂

    Using the DATEDIFF operation over about 2,000,000 rows took 00:00:12.626. Using the FLOAT operation is 00:00:12.601. This was an average of 10 times.

    In some circumstances your operation was faster.

    One other thing to note is I am using a 64-bit SKU – some operations (like float arithmetic) can be faster on x64.

    Here was the basic test script:

    CREATE TABLE ##Test
    (
    Foo DATETIME
    )

    GO

    DECLARE @tracker INT
    SET @tracker = 0

    WHILE(@tracker < 2000000)
    BEGIN
    INSERT INTO ##Test SELECT GETDATE()
    SET @tracker = @tracker + 1
    END

    GO

    SELECT DATEADD(DAY, 1, DATEDIFF(DAY, 0, Foo)) FROM ##Test
    SELECT CAST(FLOOR(CAST(Foo AS FLOAT)) AS DATETIME) FROM ##Test

    DROP TABLE ##Test

  3. August 9, 2009 at 11:16 am

    Here’s a challenge:

    Can anyone come up with a set based way to remove the DATE and keep only the TIME that will work with SQL 2005?

  4. August 11, 2009 at 4:41 am

    There is more powerful way to round the datetime to any period.

    declare @a datetime
    set @a = getdate()

    select
    @a [full],
    dateadd(minute, datediff(minute, ”, @a), ”) [minute],
    dateadd(day, datediff(day, ”, @a), ”) [day],
    dateadd(month, datediff(month, ”, @a), ”) [month],
    dateadd(year, datediff(year, ”, @a), ”) [year]

  5. shekhar R. gurav
    November 19, 2009 at 5:37 am

    sir,

    i saved all my dates in MM/dd/yyyy in ms-access database. in front end i show in dd/MM/yyyy format.

    For my update query i am taking date from different windows froms which is like
    #13/07/2009# but at database level it will it is #13/07/2009 1:12:09 AM#

    so it compare date as well as time . but i don’t want compare my date with time also…

    so what i do ?

    it is for Ms-acess…

  1. August 7, 2009 at 8:12 am

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: