Tao Klerks (4/28/2009)
Interesting approach - we use Convert, relying on the fact that DateTime values are internally stored as floating-point values, with every whole number representing a day
The FLOAT method is imprecise in the sense required for functions to be used in persisted columns. For that reason, I wouldn't use this code as the basis for a scalar UDF.
I don't suppose Microsoft are about to change the relationship between FLOATs and DATETIMEs, but it seems an unnecessary hidden dependency to add to your code.
Some folks also view this trick as a bit of a hack, when a better solution exists.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi