Stillnet Studios

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'

This entry was posted on Saturday, January 20th, 2007 at 11:51 pm and is filed under Web Development, SQL.