tl;dr
In the following code, « DateValue » and « TimeValue » can be replaced by either variables or columns of the DATE and TIME datatypes, respectively. Also note that the CONVERT functionality can be replaced with CAST, if that’s your preference.
Correct Fast Methods
These are the correct formulas that will result in the best performance without the rounding errors that are present in many other common methods found on the internet.
CONVERT(DATETIME2,CONVERT(VARBINARY(6),TimeValue)+CONVERT(BINARY(3),DateValue)) –Fastest DATEADD(dd,DATEDIFF(dd,’1900′,DateValue),CONVERT(DATETIME2,TimeValue)) –2nd Fastest Correct but Slow Methods
These formulas work correctly but they’re at least twice as slow as the correct, non-character-based methods above. I don’t recommend using