I would like to know the difference between
SELECT CAST(DateColumn AS DATE)
SELECT DATEADD(DD, DATEDIFF(DD, 0, DateColumn()), 0)
sum(case when DATEADD(dd, DATEDIFF(dd, 0,ModifiedOn), 0) between DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) AND DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) -7 THEN 1 ELSE 0 END) [0-7 Days]
Cast(DateColumn as Date)
They both do the same thing.
SQL Server introduced the
date data type in version 2008. In previous versions, there was no "convenient" way to remove the time component from a
datetime value. The solution was to calculate the number of days from some canonical point in time (say, "0") and then add those number of days back.