Steve Stedman has the low-down on time rounding in SQL Server:
One thing that I end up having to look search on regularly is rounding of dates and times in Transact SQL, having looked this up too many times I finally realized that it is time for me to do my own blog post for it.
First off, whats the difference between rounding and truncating in these examples. Rounding rounds to the closest second, so 10:00:31 is rounded up to 10:01:00, and 10:00:29 is rounded down to 10:00:00. With truncation, it simple changes the truncated area to 0’s. so 10:00:31 gets truncated down to 10:00:00, and so does 10:00:59. Sometimes you may want rounding, and sometimes you may want truncation (floor) for your specific needs.
After having used date_trunc() in Postgres, I’d really like something similar in SQL Server.