I used convert() function a lot since SQL Server does not have “Date” data type so all date you stored in system are always have a time attached to it. However, a lot of time when I created the report, I want to group the data by date, then the convert() function become a very handy function to help me to “trunc” (if you are oracle user then you know what I’m talking about) the datetime column.
Example #1: differences between before/after using the convert
select convert(varchar, getdate(),101) as date_only, getdate() as full_date_time
date_only full_date_time
----------- -------------------------
05/23/2007 2007-05-23 18:03:20.450
Example #2: Group result by day — e.g.: how many users registered each day
select convert(varchar(10), registered_date, 101) as registration_date, count(*) as user_count
from user_registration
group by convert(varchar(10), registered_date, 101)
registration_date user_count
---------------- -----------
05/15/2007 2
05/17/2007 5
05/18/2007 1
05/19/2007 10
05/20/2007 8
Example #3: includes all data in a specify date
select count(*) as user_count
from user_registration
where convert(varchar(10), registered_date, 101) = '05/15/2007'
user_count
-----------
2
Note that if you write your query as
select count(*) as user_count
from user_registration
where registered_date= '05/15/2007'
user_count
-----------
0
You are very highly possible to get zero row count, because the user have to be registered at exactly ‘2007-05-15 00:00:00′ to be able to qualify for your query criteria! See the difference? 