I don't want to beat a dead horse here, but I just discovered that you can interchange DATETIME and FLOAT values when using comparison operators like BETWEEN and > and <. So, if you accept that today, August 4, 2006 has the FLOAT equivalent of 38931.0, then you could query for records like this:
SELECT s.id, s.referer_url FROM web_stats_session s WHERE s.date_created BETWEEN 38930 AND 38932
In this case, 38930 stands for August 3, 2006 and 38932 stands for August 5, 2006. Keep in mind that the first value is inclusive (includes 08/03) and the second value is exlusive (excludes 08/05). I just think that is wicked cool.
I came across this answering a question on CF-Talk. Some guy was querying for dates but he knew that some dates would be null. I suggested this (after some testing):
SELECT id FROM [table] WHERE [date] BETWEEN ISNULL( date_started, 0 ) AND ISNULL( date_ended, getDate() )
In this example, the line:
ISNULL( date_started, 0 )
... tells the query to use the database date if it exists, and if it does not exist (is null), then use the zero date. This will not limit the query on that end of the BETWEEN. The second line:
ISNULL( date_ended, getDate() )
... tells the query to use the database date if it exists, and if it does not exist (is null), then use the current date. Now that I think about it though, if the second BETWEEN value is exclusive, you might have to add one day to the getDate().
Want to use code from this post? Check out the license.