This trick is usually useful in statistic where you need to count or sum records based on record day. Using it will help you grouping rows by day or month depends on your needs.
real life example:
My client had an Orders table where online shop orders were stored while his requirement was to create a graph that will show daily and monthly orders, so the question was how to group dates when order time was different? To convert it to varchar and then to remove time or to just group by day(orderDate),month(orderDate),yeaR(orderDate) ? Way too long, here where this trick came in use for. Basically what it does is gets number of days from zero day inside of datediff function and then adds this amount zero day to get smalldatetime type back. This trick works with days and month just instead of dd call mm
code:
select sum(productPrice) as productPrice, count(productPrice) as numOfProductsSold from Orders group by dateadd(dd,datediff(dd,0,orderDate),0)
Tags: sql