For long now, I have seen a simple algorithm to truncate the time part of a datetime value, and it look like this (originally posted by MVP Steve Kass).
It works out by calculating the whole number of days passed between the SQL Server anchor date of Monday January 1st, 1900 and the parameter value. The algorithm then adds this number of days to the anchor date and you get your wanted date without the time part.
With the arrival of SQL Server 2008 and the new DATE datatype, the time truncation is even easier
Many years ago when I read an article series by Itzik Ben-Gan about DATETIME calculations and how to truncate the date part and keep the time part, I suggested this efficient method (November 2008).
It works in a similar way as the algorithm by Steve Kass. I first calculate the number of days passed between the wanted date and SQL Server anchor date, and here lies the trick; since the wanted date is later than the anchor date, the DATEDIFF function returns a negative number. And I use this negative number to add to the parameter value so they cancel each other out. The result is that the date part is equal to SQL Server anchor date and leaving the time part intact.
Also here the calculation is simpler with SQL Server 2008
Many years has passed since then and my suggestion seem to have become the predominate algorithm to truncate the date part.
This week I again come across another interesting problem regarding time and date manipulation. How to add the time part from one source to the datepart from another source? I immediately recognized the solution because it is the same as my date-truncation algorithm. Yes it is, because I used the SQL Server anchor date as base for my calculation but you can in fact use any anchor date of your choice.
The solution and unified algorithm to put together the time part from one source with the date part from another source look like this
As you can see, the unified algorithm takes care of all scenarios because they are all the same problem. Also observe that the eventual time part for the DatePart value is not used due to the DATEDIFF function, and the date part for the TimePart value is cancelled out with the anchor date calculation.
If you are more interested in time and date, see Structure menu and various date datatypes.
The reason I’m not covering the simplest solution
is because even if it works in SQL Server 2008R2, you will get an error in SQL Server 2012. SQL Server 2012 and later no longer support normal arithmetic functions on time and date.