Join separate Date and Time fields to one field in MS SQL Server

When you have date and time separate in two fields, it becomes difficult to have date operation like add in them. To have a correct solution, we need to join these two fields and create a new DateTime value on which we can operate on. Use the following code in such situations.

Table

|ID|StartDate|StartTime|

See the query below which takes the hour and minutes from the time field (StartTime) and convert it into seconds. Then DateAdd these seconds into the date field (StartDate) to create a new field. This way, the new field will have a proper DateTime value which can be operated on.

SELECT ID, DATEADD(SECOND,((DATEPART(hour, StartTime) * 3600) + (DATEPART(minute, StartTime) * 60)+ DATEPART(second, StartTime)),StartDate) as NewStartDate