Sometimes you have the need in an application to compare dates without the time component. For example, a user signs up for a free 30-day trial of a client’s site. The user’s ‘expire_date’ will be set for 30 days from today, probably using something like DateAdd(“d”,30,getdate()). At some point you need to compare the expire_date against the current date. Maybe its a report of current free trial users, or a query to see if the user is allowed to login. Your query will look something like

... WHERE expire_date >= getdate()

The problem with this is that both values contain a time component. What you are really comparing is something like

... WHERE 2007-1-20 10:44:22.717 >= 2007-1-20 10:30:28.287

So a user may be considered still in their free trial at 10:30am, but when they try to login again 20 minutes later, they may be told their trial has expired. In reality the free trial should continue throughout the last day of the trial period. If you signed up for a free two week gym membership, would you expect to be allowed in before lunch on the last day but not after because of what time you initially signed up?

This is not a problem in MySQL, as it has a simple DATE type (in addition to the common DATETIME type) that does not store any time data. So what we need to do in SQL Server is strip off the time component. I typically do this when I store the date, but you could do it at compare time, too.

SQL Server datetimes are stored internally as floating point numbers. You can see this by CASTing the value of getdate to a float.

SELECT CAST( getdate() AS float)

When I ran this just now I got 39100.964272067904. That indicates 39100 days after 1/1/1900 (SQL Server’s base date), and 96/100ths of a fractional day (it’s almost midnight). We can use this to modify the float value and then CAST it back as a datetime type. The FLOOR function will strip off any decimal portion of a number. Thus:

SELECT CAST(FLOOR(CAST( getdate() AS float)) AS datetime)

returns “2007-01-20 00:00:00.000″ – the date without the time.

There are other ways to do this, using the day, month and year functions or the CONVERT function. I find the above method to be more straight forward (although only by a little – its a pain no matter how you do it). You could add that code to a trigger so whenever that field is set or updated the time portion is automatically stripped off.

Now when we compare the field, we’ll need to do the same conversion to the current day:

... WHERE expire_date >= CAST(FLOOR(CAST( getdate() AS float)) AS datetime)

Keep in mind that queries with functions in the search condition are expensive. If you are going to run this often or on a lot of data, you may want to pass in the date from your application language:

... WHERE expire_date >= '2007-01-20'

35 Comments

  1. john says:

    how about Comparing time without dates in SQL Server??

  2. Marlene says:

    How do I replace getdate() with query criteria entered by the user.

    I.e., select *
    from [my table]
    where CAST(FLOOR(CAST( [my date field] AS float)) AS datetime) =
    (SELECT CAST(FLOOR(CAST( 07/23/2007 AS float)) AS datetime))

    Thank you. This is very cool and I’d like to use it if I can replace getdate() with my own date value.

  3. Marlene says:

    Sorry, I should’ve given myself 5 more minutes to figure it out. Unless there’s a better way than…

    select *
    from [my table]
    where CAST(FLOOR(CAST( [my date field] AS float)) AS datetime) =
    (SELECT CAST(FLOOR(CAST( cast (’07-23-2007′ as datetime) AS float)) AS datetime))

    I don’t know if there’s a simpler, less confusing way to do it.

  4. Azizah says:

    I’m confuse already. But your post really contain lots of information on SQL server that I don’t understand much about before.

  5. Kysy says:

    Nice, but this solution don’t use indices related to datetime or smalldatetime fields. Very baaad in many situations!

    WHERE CAST(FLOOR(CAST( cast (getdate() as datetime) AS float)) AS datetime))

  6. Instructor says:

    Thanks a lot for this tip. I was looking exactly for that!

  7. John says:

    Thanks it helped a lot.

  8. Manin says:

    like mentioned in the article, we could also compare by having 3 condition which is day,month and year.. its easier and clearer to understand but more syntax involved. was lookin for an alternative and i think this is it .

  9. Jeff says:

    Thanks for the help. It saved me a lot of trouble

  10. David says:

    I would simply using the following to create ‘expire_date’ .

    DateAdd(“d”,31,convert(varchar(10), getdate(), 102) )

    The system will see it as Midnight 31 days from now, which is technically when you would want to cutoff.

    Then you don’t have to worry about the time factor later.

  11. Ulises Moreno says:

    Very Helpful information, this is the first time i see this compare way

  12. smcleod says:

    You just saved me a bunch of time and made this much simpler in our code. Thanks SO MUCH for sharing this!

  13. José says:

    I find it more straightforward to use DATEDIFF.

    if DATEDIFF ( day , startdate , enddate ) = 0 then it is the same date.

  14. wilmsoft says:

    Why in the world doesn’t MSSQL just have a f-ing DATE field? (Others do… ) ;)

  15. andrew says:

    Thankyou very much for this, ive been trying to do this for the past hour !!

  16. Aidan says:

    The other way that i use when trying to match up Date’s is to use the DATEPART funciton.

    Select *
    from Test AS T
    WHERE DATEPART(DY, T.DateValue) = DATEPART(DY, GETDATE())
    AND DATEPART(YY, T.DateValue) = DATEPART(YY, GETDATE())

    The DY paramters returns the Day of Year. This has the advantage that you can store this value in a table if you need to (although it requires 2 fields).

    If you just need to test for equality, however, its probably easier to use the DATEDIFF function that was mentioned above.

  17. Rich says:

    Thankyou!  This is the best article about date manipulation I have ever read and very true to life.  Whoever wrote this deserves a medal!

  18. Ryan says:

    Another cool way a friend showed me of doing this is …

    SELECT CONVERT(DATETIME,CONVERT(VARCHAR(12), GETDATE(), 105))

    … I think you can get the different codes (e.g. 105) in the msdn cast/convert section.

    I like the first solution though. Shows how many tricks can be learned in sql :)

  19. Ting says:

    Great aritcle! That's exactly I was looking for!

    Thanks! :)

  20. Justin Schier says:

    I made a function that I use called WholeDay() so you can just wrap any date in WholeDay(DATE_COLUMN). This is SQL Server, and I always have to use dbo.WholeDay(DATE_COLUMN)

    Here's the function:

    /****** Object:  UserDefinedFunction [dbo].[WholeDay]    Script Date: 05/10/2009 11:55:50 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    – =============================================
    – Author:        Justin Schier
    – Create date: 4/9/2008
    – Description:    Drops Time from DateTime
    – =============================================
    CREATE FUNCTION [dbo].[WholeDay]
    (
        – Add the parameters for the function here
        @p1 datetime
    )
    RETURNS datetime
    AS
    BEGIN
        – Declare the return variable here
        DECLARE @Result datetime

        – Add the T-SQL statements to compute the return value here
        SELECT @Result = CAST(FLOOR(CAST(@p1 AS FLOAT)) AS DATETIME)

        – Return the result of the function
        RETURN @Result

    END

  21. Justin Schier says:

    In the above code, the dashes should be double dashes. This website changed them to long dashes.

  22. john persona says:

    great article, but it does not explain how to get only time portion from full date.

  23. Scott Jibben says:

    If you want just the time portion from a datetime column, read the information on the CONVERT() function in the docs for more info.

    (12 hour)

    SELECT CONVERT(varchar(12), myDateField, 8) FROM myTable;

    or (24 hour)

    SELECT CONVERT(varchar(12), myDateField, 14) FROM myTable;

  24. Scott Jibben says:

    This web editor converted the 8, followed by a right parenthesis into a smiley face…

  25. Marianne says:

    Thank you! This was exactly what I needed :)

  26. Blerta says:

    Great article and big help. Thank you!

  27. Claes says:

    If you compare two datetime fields in this way, the cast back to datetime should not be necessary. This saves some execution time.

  28. Manoj says:

    can we able to compare two dates by using if condition

  29. Faheem Ahmad says:

    Great article.
    I tested with following example and it really works for me.

    DECLARE @d1 SMALLDATETIME
    DECLARE @d2 SMALLDATETIME

    SET @d1 = (SELECT CAST(FLOOR(CAST( GETUTCDATE() AS float)) AS datetime))
    SET @d2 = (SELECT CAST(FLOOR(CAST( GETDATE() AS float)) AS datetime))

    –PRINT @d3
    –PRINT @d4

    IF(@d1 = @d2)
        PRINT 'both dates are equal'
    ELSE
        PRINT 'no both dates are different'
        
    Thanks dude.

  30. Javier says:

    This is the best way by far:

    DateAdd(day, datediff(day,0, MydateValue), 0)

    See the complete article on : http://stackoverflow.com/questions/353014/convert-sql-server-datetime-fields-to-compare-date-parts-only-with-indexed-looku

  31. Ankur says:

    SELECT * FROM TableName WHERE DATEPART(DY,Date) = DATEPART(DY,@date)

  32. kenny says:

    What’s wrong with:
    select cast(datetimefield as date) ?

    Why go the hard way?
    I tried it, and just casting to date type works, no floor, floats, .. involved..

  33. Ryan says:

    Kenny, thanks for the comment. The ‘date’ type was only added in SQL Server 2008. In all the prior versions you’ll need to do some mangling to be able to compare just the date portion.

  34. dave says:

    Like Jose said… use datediff(day, logindate ,’2012/06/05′ ) = 0

    Query Optimizer can use index on date that way.

  35. Rodger says:

    WHERE CAST(expire_date AS DATE) >= CAST(getdate() AS DATE)

Leave a Reply

You must be logged in to post a comment.