How build a SELECT statement with dates disconsidering time?

Question

Hi. I have a site with a SELECT statement like this:

 SELECT * FROM MyTable WHERE MyDate >= GETDATE()

I have a record with the value 6/24/2009 9:00:00 AM to MyDate field.

When the user visit this page in 6/24/2009 before 9:00:00 AM, this record is shown. But when the user visit this page in 6/24/2009, but after 9:00:00 AM, the SELECT don't get this record.

How can I build this SELECT statement disconsidering time, to this record appers until 6/24/2009 12:00:00 PM?

Thank you.

Answer 1

Do you really mean 12:00 PM? That's noon, not midnight. Sounds like you really want somthing more like this:

SELECT * 
FROM MyTable 
WHERE cast(floor(cast(MyDate as float))+1 as datetime) > getdate()

See this:
http://stackoverflow.com/questions/923295/how-to-truncate-a-datetime-in-sql-server

The

Answer 2

MCardinale, you already have an answer but I wanted to give one extra piece of advice.

Be very sure that you want to do it that way. Per-row functions in SELECT statements never scale very well as the database tables become bigger (not just the one in the accepted answer, I mean any per-row function). That's because they tend to break the ability of the DBMS to choose the correct execution plan for maximum speed.

If you want a solution to scale well, you need to store information in the DB in such a way as the extraction speed is blindingly fast (based on the maxim, some would say truism, that table rows are almost always read far more often than they're written).

To that end, a workable solution is often to add an entire new column which just contains the date portion of the given datetime column and use insert/update triggers to ensure it's set correctly.

Then, by having an index on that new column, queries on the date of each row can be done without having to worry about the performance of per-row functions.

The impact of the trigger on performance shouldn't matter since inserts and updates happen far less than selects. What this solution does is to shift the cost of converting datetimes to dates off to the insert/update rather than the select, greatly reducing the cumulative cost.

This becomes apparent in a database table that never changes once set up - the cost in your current situation happens (unnecessarily) every time you read the data. In the situation described in this answer, the cost disappears totally.

Of course it will need more disk space for the table but rarely have I ever seen disk space as the bottleneck in a DBMS - it's almost always raw CPU grunt.

Now I realize that this may break 3NF (since that new column may not be dependent on the key) but often that comes in handy for performance. The general rule I follow is to implement in 3NF and drop back to other normalized forms if performance becomes an issue.

Just some food for thought - enjoy.

Post Scriptum: If you are really storing a datetime in your field, you probably should steer clear of calling it MyDate. If it is really intended to be a date, the column type is wrong (and my advice here becomes moot). Otherwise, it should be renamed to something more descriptive. The DBAs and code-cutters that follow you will be eternally grateful :-)

Answer 3

You can perform the date arithmetic on the getdate() so that the query optimizer has the chance to use any indexes on MyDate.

SELECT * 
FROM MyTable 
WHERE MyDate >= cast(floor(cast(getdate() as float)) as datetime)