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

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?

CodePudding user response：

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'.