There are various ways to ignore the time portion of a field. I already showed you one way (which happens to be the best way).
Code:
select stats.*
from stats
where datestat >= '20080709'
And datestat < '20080710'
Notice that datestat must be greater than or equal to 20080709 (July 9, 2008) AND it must be less than 20080710 (July 10, 2008). This query will return all the rows for July 9, 2008. This query also has the benefit of being [google]sargable[/google]. This means, if there is an index on the datestat column, the query engine will be able to use the index to return the rows faster (better for performance).
SQL Server stored DateTime data (behind the scenes) as 2 integer values, one representing the date and another representing the time. However, it often helps to think of DateTime values and floats. The whole number represents the number of days that have elapsed since Jan 1, 1900 and the fractional part represents fractional days (0.5 = noon, 0.25 = 6 AM, 0.75 = 6 PM, etc...).
When you think of DateTime this way, many harder to understand concepts fall in to place.
My favorite method for removing the time component is to do a little date manipulation. SQL Server has a couple of functions that will help here. There's a DateDiff function that returns an INTEGER in whatever interval you specify. There's also a handy DateAdd function. Used together, you can remove the time component, like this...
[tt][blue]
[green]-- Current DateTime[/green]
Select GetDate()
[green]-- Integer Number of days that have elapsed since Jan 1, 1900[/green]
Select DateDiff(Day, '19000101', GetDate())
[green]-- Add the Whole number of days back to Jan 1, 1900[/green]
Select DateAdd(Day, DateDiff(day, '19000101', GetDate()), '19000101')
[/blue][/tt]
For another method, you can remove the time component by doing a little math manipulation. SQL Server has a FLOOR function that will round down to the nearest integer ( Floor(1.99) = 1 ). So, we can convert the DateTime value to Float, floor is, and convert back to datetime. This has the effect of removing the time component (setting it to 12:00:00).
Ex:
[tt][blue]
[green]-- Returns current Date/time[/green]
Select GetDate()
[green]-- Returns float representation[/green]
Select Convert(Float, GetDate())
[green]-- Removes fractional component[/green]
Select Floor(Convert(Float, GetDate()))
[green]-- Convert back to DateTime, no time component[/green]
Select Convert(DateTime, Floor(Convert(Float, GetDate())))
[/blue][/tt]
There's a third method, which involves string conversions. Strings, in any language, are slower than math manipulation, so I don't recommend this method.
[tt][blue]
[green]-- Current DateTime[/green]
Select GetDate()
[green]-- Convert with style argument = 112 (removes the time)[/green]
Select Convert(VarChar(20), GetDate(), 112)
[green]-- Convert back to DateTime[/green]
Select Convert(DateTime, Convert(VarChar(20), GetDate(), 112))
[/blue][/tt]
So, there you have 3 methods to remove the time component. You can use this in a query, like this...
Code:
select stats.*
from stats
where [!]DateAdd(Day, DateDiff(Day, 0, [/!]datestat[!]),0)[/!] = '20080709'
Code:
select stats.*
from stats
where [!]Convert(DateTime, Floor(Convert(Float, [/!]datestat[!])))[/!] = '20080709'
Code:
select stats.*
from stats
where [!]Convert(DateTime, Convert(VarChar(20), [/!]datestat[!], 112))[/!] = '20080709'
The problem with these 3 queries is that it is not sargable. If you have an index on the DateStat column, it will NOT be used by the query optimizer, and your performance will suffer. In fact, the only reason I mention it is because it helps to understand how DateTime values are handled.
You probably noticed the way I hardcode dates in all the queries. I am using the ISO Unseparated date format, which is "YYYYMMDD hh:mm:ss". You see, depending on the language of the login used to make the connection to SQL Server, dates are handled differently. Some areas of the world use mm/dd/yyyy and other places used dd/mm/yyyy. So, 3/4/2008 could represent March 4 or April 3, depending on your language. By using the ISO unseparated date format, there is no ambiguity, so SQL Server will always interpret it correctly.
I know there's a lot of information here. If there is anything you don't understand, please let me know and I will explain more. Properly handling dates is important. It's not hard, but there is plenty of opportunity to do it wrong. Once you understand the concepts I present here, most of your date handling code will make more sense.
I hope this helps.
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
