Hey guys,

I am trying to design a query that will return all the records based on a single date but because of the date/time data type in MS SQL 2005 it defaults to the date and the time 00:00:00 which will return no records for me.

Is it possible to ignore the time part in the select statement?


Here is the way I deprecate a standard datetime (5/7/2008 23:28) to a smalldate (5/7/2008) in my sql statements:

Cast(Floor(Cast(myDateTimeField as float)) as datetime)

This first converts the datetime field myDateTimeField to a float. The float is an accurate representation of time. I beleive that the computation is based on how many days its been since some date in the past. For instance today it is 39573 days past this past date. If the number returned was 39573.25 I would know that the time value is 6am on the same date. By flooring what the inner cast returns you are chopping off the time from the datetime field, then you cast it back to a datetime in order for it to be a date again. Only this time its 5/7/2008 00:00:00. This is the best way I know of to remove time from a datetime when performing date arithmetic / applying date logic.

Hope this helps.