I've come across several clever and convenient tricks for dealing with dates recently in SQL Server. I previously blogged about how to remove time from date/time stamp in SQL Server, but I've come across a group of other really useful date/time manipulations that can be very useful when dealing with SQL Server.
The basic idea behind most of these tricks is that by using a known base datetime stamp, you can then use simple addition and subtraction to calculate the new dates. For example, if I calculate the difference in days between Jan 9, 2008 at 08:00 Jan 1, 2000 at 00:00, I'll end up with a whole number of days which I can then and add back to Jan 1, 2000 at 00:00 and I'll end up with Jan 9, 2008 at 00:00.
Take the tricks below and play around to see what other useful combinations you can develop. If you have any other useful date/time tricks, let me know!