共用方式為

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

Enjoy.

Comments

  • Anonymous
    February 24, 2014
    cast(begin_date as datetime) + cast(begin_time as datetime)
  • Anonymous
    September 02, 2014
    or just add a string to a datetime:declare @date as datetime = '2013-08-09 00:00:00.000'declare @time as nvarchar(8) = '15:33:26'select @date, @time, @date+@time(No column name)                  (No column name) (No column name)2013-08-09 00:00:00.000 15:33:26                  2013-08-09 15:33:26.000
  • Anonymous
    December 10, 2015
    thanks :)