MS SQL: Custom Date and Time functions

Transact-SQL really lack a the neccessary support for creating and modifying dates and times. Now and then I have the need for creating custom dates based on year, month and day. This is not an easy task. To simplify the manipulation of dates and times a small toolkit of userdefined functions has been developed:


Date and Time functions

• Create a date from year, month and day
• Create a DateTime value from year, month, day, hours, minutes and seconds
• Create a time structure for a given date
• Extract the date portion of a DateTime value
• Extract the time portion of a DateTime value

Calendar functions

• Get the Day of Week for a given date
• Get the First Day of Week

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: Date portion from DateTime

To return only the date portion of a DateTime variable, you need to use a combination of the dateadd and datediff functions in Transact-SQL.

-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.

CREATE  function [dbo].[sudf_Common_DateOnly]

(

@dtDateTime DateTime

)

returns datetime

as

begin

      -- Get the date only

    return dateadd(dd, 0, datediff(dd, 0, @dtDateTime))

end

MS SQL: Time portion from DateTime

To get only the Time portion of a DateTime variable in Transact-SQL, you can use the following function:

-- Returns only the time portion of a DateTime, at the "base" date (1/1/1900)

CREATE function [dbo].[sudf_Common_TimeOnly]

(

      @dtDateTime datetime

)

returns datetime

as

begin

      -- Get the time only

    return dateadd(day, -datediff(day, 0, @dtDateTime), @dtDateTime)

end

Please notice that the base date is January 1st 1900. Any dates/times prior to this day won't work,