Introduction to functions

In SQL Server we have the DATEADD() and DATEPART() functions, which are very useful in the case of having to add a value of type Time with another of type Date to create a value of type Datetime.

The DATEADD() and DATEPART() functions allow respectively to add or obtain a numeric value as part of a time or a date, such as days, hours and minutes, adding or extracting to/from a given value of type Time or Date.

Example of DATEADD() that adds a year to the current date (more info):

SELECT DATEADD(year, 1, CURRENT_TIMESTAMP)

DATEPART() example that gets the day of the month of the current date (more info):

SELECT DATEPART(day, GETDATE())

Demonstration

The following sql script creates a test table in which the sum in milliseconds of a value of type Time with another of type Date is carried out:

-- Create test table
DECLARE @TestTable TABLE (
	                      [MY_DATE] date,
	                      [MY_TIME] time,
	                      [MY_DATETIME] datetime
                         );

-- Add a record in the table
INSERT INTO @TestTable VALUES (
                               CONVERT(date, GETDATE()), -- current date
                               CONVERT(time, GETDATE()), -- current time
                               NULL
                              );

-- Sets the value of MY_DATETIME by adding the time in milliseconds from MY_TIME to MY_DATE
UPDATE @TestTable
SET MY_DATETIME = DATEADD(millisecond, (
                                        DATEPART(hour,MY_TIME)*3600000 +
                                        DATEPART(minute,MY_TIME)*60000 +
                                        DATEPART(second,MY_TIME)*1000 +
                                        DATEPART(millisecond,MY_TIME)
                                       ),
				          CAST(MY_DATE as datetime)
				         )

SELECT * FROM @TestTable

Formulas

Next we will see the different ways in which we can add a time to a date.

Add hours:

SELECT DATEADD(hour, DATEPART(hour,MY_TIME), CAST(MY_DATE as datetime))

Add hours and minutes:

SELECT DATEADD(minute, (
                        DATEPART(hour,MY_TIME)*60 +
                        DATEPART(minute,MY_TIME)
                       ),
               CAST(MY_DATE as datetime)
              )

Add hours, minutes and seconds:

SELECT DATEADD(second, (
                        DATEPART(hour,MY_TIME)*3600 +
                        DATEPART(minute,MY_TIME)*60 +
                        DATEPART(second,MY_TIME)
                       ),
               CAST(MY_DATE as datetime)
              )

Add hours, minutes, seconds and milliseconds:

SELECT DATEADD(millisecond, (
                             DATEPART(hour,MY_TIME)*3600000 +
                             DATEPART(minute,MY_TIME)*60000 +
                             DATEPART(second,MY_TIME)*1000 +
                             DATEPART(millisecond,MY_TIME)
                            ),
               CAST(MY_DATE as datetime)
		      )