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: 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: 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: First Day of Week

In Transact-SQL the first day of week can be calculated based on the @@FirstDay system variable.
The variable will return different values depending of configured language of the server. For US English the first day of week will be set to 7, i.e. Sunday. In .NET a different day range is used.
We can use the following calculation to ensure that Sunday is 0, Monday = 1, etc.

 @@datefirst % 7


The full user defined function is as follows:

 --   Description: Returns the first weekday of the week
--    based on the system settings on the DB server:
--                              0 - Sunday
--                              1 - Monday
--                              2 - Tuesday
--                              3 - Wednesday
--                              4 - Thursday
--                              5 - Friday
--                              6 - Saturday
--                             
-- =============================================
ALTER FUNCTION [dbo].[sudf_Calendar_FirstDayOfWeek]
(      
)
RETURNS int
AS
BEGIN
        -- Variables
        declare @intFirstDayOfWeek  int;

        -- The first day of week
        set @intFirstDayOfWeek = (@@datefirst % 7);
       
        -- Return the first day of the week
        return @intFirstDayOfWeek;
END

MS SQL: Day of Week

The day of week for a given date can in Microsoft SQL server be calculated based on the @@FirstDay system variable and the datepart function in Transact-SQL. The value returned from datepart is not constant but depends on the first day of week specified by the @@FirstDay variable. In modern programming languages like C# we will get a constant value for each day of the week. In .Net the DayOfWeek function will return 0 for sundays, 1 for mondays, etc.

A constant DayOfWeek value can be calculated in the following way:
(((@@datefirst-1) + datepart(weekday, @dtDate)) % 7)

The full Transact-SQL userdefined function is as follows:

-- =============================================

-- Description: Returns the weekday number of a given date

--

--    0 - Sunday

--    1 - Monday

--    2 - Tuesday

--    3 - Wednesday

--    4 - Thursday

--    5 - Friday

--    6 - Saturday

--                             

-- The DayOfWeek is calculated based on the current

-- @@DateFirst settings

-- between the current date and the beginning of the week

-- =============================================

CREATE FUNCTION [dbo].[sudf_Calendar_DayOfWeek]

(      

        @dtDate DateTime        -- Current date

)

RETURNS int

AS

BEGIN

        -- Variables

        declare @intDayOfWeek   int;

        -- Get the day of week

        set @intDayOfWeek = (((@@datefirst-1) + datepart(weekday, @dtDate)) % 7);

        -- Calculate the offset

        return @intDayOfWeek;

END

Source: Coragi.com