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'
john says:
how about Comparing time without dates in SQL Server??
19 July 2007, 8:52 pmMarlene 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.
23 July 2007, 6:15 pmMarlene 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.
23 July 2007, 6:21 pmAzizah says:
I’m confuse already. But your post really contain lots of information on SQL server that I don’t understand much about before.
7 August 2007, 3:02 amKysy 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))
28 August 2007, 4:46 amInstructor says:
Thanks a lot for this tip. I was looking exactly for that!
6 September 2007, 3:53 amJohn says:
Thanks it helped a lot.
5 October 2007, 10:52 amManin 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 .
4 November 2007, 11:34 pmJeff says:
Thanks for the help. It saved me a lot of trouble
5 December 2007, 11:37 amDavid 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.
26 December 2007, 2:56 pmUlises Moreno says:
Very Helpful information, this is the first time i see this compare way
13 May 2008, 4:31 pmsmcleod says:
You just saved me a bunch of time and made this much simpler in our code. Thanks SO MUCH for sharing this!
26 June 2008, 9:55 amJosé says:
I find it more straightforward to use DATEDIFF.
if DATEDIFF ( day , startdate , enddate ) = 0 then it is the same date.
29 June 2008, 5:47 pmwilmsoft says:
Why in the world doesn’t MSSQL just have a f-ing DATE field? (Others do… ) 😉
30 July 2008, 12:23 pmandrew says:
Thankyou very much for this, ive been trying to do this for the past hour !!
22 August 2008, 6:45 amAidan 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.
21 October 2008, 9:41 pmRich 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!
30 January 2009, 10:20 amRyan 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 🙂
11 February 2009, 4:46 amTing says:
Great aritcle! That's exactly I was looking for!
Thanks! 🙂
8 May 2009, 1:54 pmJustin 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
10 May 2009, 1:57 pmJustin Schier says:
In the above code, the dashes should be double dashes. This website changed them to long dashes.
10 May 2009, 1:58 pmjohn persona says:
great article, but it does not explain how to get only time portion from full date.
12 May 2009, 1:38 amScott 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;
4 June 2009, 3:16 pmScott Jibben says:
This web editor converted the 8, followed by a right parenthesis into a smiley face…
4 June 2009, 3:18 pmMarianne says:
Thank you! This was exactly what I needed 🙂
9 June 2009, 2:31 pmBlerta says:
Great article and big help. Thank you!
31 July 2009, 5:52 amClaes says:
If you compare two datetime fields in this way, the cast back to datetime should not be necessary. This saves some execution time.
25 August 2009, 3:13 amManoj says:
can we able to compare two dates by using if condition
25 July 2010, 8:00 amFaheem 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)
12 April 2011, 2:00 amPRINT 'both dates are equal'
ELSE
PRINT 'no both dates are different'
Thanks dude.
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
12 April 2011, 3:43 pmAnkur says:
SELECT * FROM TableName WHERE DATEPART(DY,Date) = DATEPART(DY,@date)
24 September 2011, 7:09 amkenny says:
What’s wrong with:
select cast(datetimefield as date) ?
Why go the hard way?
3 November 2011, 3:34 amI tried it, and just casting to date type works, no floor, floats, .. involved..
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.
3 November 2011, 6:15 amdave says:
Like Jose said… use datediff(day, logindate ,’2012/06/05′ ) = 0
Query Optimizer can use index on date that way.
20 June 2012, 10:13 amRodger says:
WHERE CAST(expire_date AS DATE) >= CAST(getdate() AS DATE)
19 July 2012, 7:20 am