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 Date

There is no straight forward function in Transact-SQL to create a simple date based on year, month and day. Below we have created a userdefined function to handle this. Notice that the base date in Microsoft SQL Server is January 1st 1900. We generate the date by adding the year, month and day to the base date.

CREATE function [dbo].[sudf_Common_Date]

(

      @intYear int,

      @intMonth int,

      @intDay int

)

-- returns a datetime value for the specified year, month and day

returns datetime

as

begin

      -- Returns a date later than the base date (1.1.1900)

    return dateadd(month,((@intYear-1900)*12)+@intMonth-1,@intDay-1)

end