Adventures in TSQL: Adding date and time values

With the addition of the SQL Server date and time types, I have often found myself needing to create a datetime (or datetime2) value based on the addition of a date and a time value. However, there is no built-in function for such an operation.

There are a few solutions to this problem that encompass processing strings; such as:

DECLARE @nowdate date = SYSDATETIME();
DECLARE @nowtime time = SYSDATETIME();

SELECT CONVERT(datetime2,CONVERT(varchar, @nowdate, 112) + ' ' + CONVERT(varchar, @nowtime, 108))

However, if one realizes that when a time value is cast to a datetime the date component is set to '1900-01-01', and this date equates to a days equivalent of zero in the SQL date and time functions. Thus the following user-defined function can be used to add a date and time value:

CREATE FUNCTION [dbo].[DateTimeAdd]
(
      @datepart         date,
      @timepart         time
)
RETURNS datetime2
AS
BEGIN
      RETURN DATEADD(dd, DATEDIFF(dd, 0, @datepart), CAST(@timepart AS datetime2));
END

This works as the DATEDIFF function with a zero value and a date returns the number of days from the date '1900-01-01'. Thus adding the DATEDIFF result to a time value cast to a datetime value, effectively adds the date and time values.

Thus one can now write:

DECLARE @nowdate date = SYSDATETIME();
DECLARE @nowtime time = SYSDATETIME();

SELECT dbo.DateTimeAdd(@nowdate, @nowtime);

In the same manner if one wanted to extract just the date portion of a datetime variable, one option is to perform a cast to and from a date type. However one can also start from day 0 and add the corresponding number of days:

CREATE FUNCTION [dbo].[DatetimeDateOnly]
(
    @datetime datetime2
)
RETURNS datetime2
AS
BEGIN
    RETURN DATEADD(dd, 0, DATEDIFF(dd, 0, @datetime));
END

Written by Carl Nolan