MS SQL: Create DateTime

To create a full DateTime structure in Transact-SQL we need to use two of our earlier defined userdefined functions.

 

 First we generate a raw base date, and then we add the hours, minutes and seconds.

 

-- Returns a dateTime value for the date and time specified.
create function [dbo].[sudf_Common_DateTime]

(

      @intYear    int,

      @intMonth   int,

      @intDay     int,

      @intHour    int,

      @intMinute  int,

      @intSecond  int

)

returns datetime

as

begin

      -- Create the datetime structure

      return dbo.sudf_Common_Time(@intHour, @intMinute,@intSecond, dbo.sudf_Common_Date(@intYear, @intMonth, @intDay))

end

MS SQL: Create Time

To create a new DateTime value for a given date and a given time, we can use the userdefined function below. We start by retrieving the raw date (no time portion) of the base date, and then add the hours, minutes and seconds.

-- Returns a datetime value for the specified time at the "base" date (1/1/1900)

CREATE function [dbo].[sudf_Common_Time]

(

      @intHour    int,

      @intMinute  int,

      @intSecond  int,

      @dtBaseDate datetime

)

returns datetime

as

begin

      -- Build the time

    return  dbo.sudf_Common_DateOnly(@dtBaseDate) +

                  dateadd(ss,(@intHour*3600) + (@intMinute*60) + @intSecond,0)

end