T-SQL Add new column in existing table and fill with value from two another existing column

Question

I have a table test with the following columns: id, startDate, stopDate, startOvertime, stopOvertime.

startDate and stopDate are of type datetime and startOvertime, stopOvertime are of type time(7).

I want to create a new column with the date portion from stopDate and the time portion from startOvertime and copy that into a newly created column test-fill.

If I use formula (stopDate, startOvertime) then the value of the new datetime column is not right. Any proposals?

Answer 1

If you always want the new column with this information, then you can use a computed column:

alter table test add test-fill as
    (cast(cast(startDate as date) as datetime) + cast(overtime as datetime));

This doesn't actually add a new column. It just computes the values when you need them.