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: 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

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

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,

MS SQL: Constants

Transact-SQL does not offer a good way to support constants in your stored procedures or userdefined functions. This means that you either can choose to hardcode your constants or, the more elegant way, define a userdefined function for each "constant" value. To ensure optimal performance, the schemabinding keyword should be used.

Here is an example:

CREATE FUNCTION [dbo].[sudf_Security_Privilege_User]

(

)

RETURNS INT

with schemabinding

AS

BEGIN

      -- Return the access level

      RETURN (1);

END

In your stored procedure you can call the function the normal way, i.e.

set x = dbo.sudf_Security_Privilege_User();