What is the neat approach to combining the date and time value (both of datetime type)?

I am writing a SQL query on table that has 2 columns:

• StartDate: type – datetime (example: 2022-01-22 00:00:00.000)
• StartTime: type – datetime (example: 1900-01-01 21:30:00.000)

I want to combine both so as to get the datetime value (example: 2022-01-22 21:30:00.000)

I tried using the DATEADD function, but that expects an interval, which is not suitable for my requirement.

I also tried adding the dates using the + sign which seems to give correct result; and also tried converting the date to int and then doing the + followed by conversion to datetime. This doesn’t give correct result.

What is the neat approach to combining the date and time value?

>Solution :

You can simply add the two parts, since the reference date for datetime is 1900-01-01. I.e., internally SQL-Server represents it by the number 0.

select
    StartDate, StartTime,
    StartDate + StartTime as StartDateTime
from t

See: http://sqlfiddle.com/#!18/0f0a7/2/0

The documentation for datetime (Transact-SQL) just says

Default value 1900-01-01 00:00:00

and

When the conversion is from time(n), the time component is copied, and the date component is set to ‘1900-01-01’.