FLOOR a DateTime in SQL Server

Suppose you are given a datetime and want to emulate the FLOOR function on it eg: find first day of year, first day of month etc, you can use the following code



declare @date datetime
set @date=' 2011-05-17 06:36:22.252'
select
    DATEADD(year,datediff(year,0,@date),0) as year,
    DATEADD(month,datediff(month,0,@date),0) as month,
    DATEADD(day,datediff(day,0,@date),0) as day,
    DATEADD(hour,datediff(hour,0,@date),0) as hour,
    DATEADD(minute,datediff(minute,0,@date),0) as minute,
    DATEADD(day,datediff(day,0,@date),0) +convert(varchar(15),@date,108)as seconds ,
    @date as milliseconds

In the above code, datediff finds the difference in terms of parameter (year, month, etc)
and it is added to the base date 0, which omits the month, day,time part  etc.

OUTPUT

(contd…)