Wednesday, March 21, 2007

SQL Tips - Date and Time User-Defined Functions

Introduction
Date and Time UDFs
  • DatePart
  • TimePart
  • GetWorkingDays
  • AddWorkingDays
  • FirstMonthDay
  • LastMonthDay

    • Introduction

    I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

  • Date and Time User-Defined Functions
  • Mathematical User-Defined Functions
  • Metadata User-Defined Functions
  • Security User-Defined Functions
  • String User-Defined Functions
  • System User-Defined Functions
  • Text and Image User-Defined Functions


    • In this article, I wrote some useful Date and Time User-Defined Functions.

    Date and Time UDFs

    These scalar User-Defined Functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

    DatePart

    Returns the date part of the datetime value.

    Syntax

    DatePart ( datepart )

    Arguments

    datepart - datetime value.

    Return Types

    varchar

    The function's text:

    CREATE FUNCTION dbo.DatePart
     ( @fDate datetime )
    RETURNS varchar(10)
    AS
    BEGIN
     RETURN ( CONVERT(varchar(10),@fDate,101) )
    END
    GO

    Example

    This example returns a character string contained the date part of the datetime value:

    SELECT dbo.DatePart('11/11/2000 11:15AM')

    Here is the result set:

    ----------
    11/11/2000

    (1 row(s) affected)

    TimePart

    Returns the time part of the datetime value.

    Syntax

    TimePart ( datepart )

    Arguments

    datepart - datetime value.

    Return Types

    varchar

    The function's text:

    CREATE FUNCTION dbo.TimePart
     ( @fDate datetime )
    RETURNS varchar(10)
    AS
    BEGIN
     RETURN ( CONVERT(varchar(7),right(@fDate,7),101) )
    END
    GO

    Example

    This example returns a character string contained the time part of the datetime value:

    SELECT dbo.TimePart('11/11/2000 11:15AM')

    Here is the result set:

    ----------
    11:15AM

    (1 row(s) affected)

    GetWorkingDays

    Returns the number of working days between two dates (not including these dates).

    Syntax

    GetWorkingDays ( StartDate, EndDate )

    Arguments

    StartDate - the datetime value (start date).
    EndDate - the datetime value (end date).

    Return Types

    int

    The function's text:

    CREATE FUNCTION dbo.GetWorkingDays
     ( @StartDate datetime,
       @EndDate datetime )
    RETURNS INT
    AS
    BEGIN
     DECLARE @WorkDays int, @FirstPart int
     DECLARE @FirstNum int, @TotalDays int
     DECLARE @LastNum int, @LastPart int
     IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
       BEGIN
         RETURN ( 0 )
       END
     SELECT
      @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
      @FirstPart = CASE DATENAME(weekday, @StartDate)
                    WHEN 'Sunday' THEN 6
                    WHEN 'Monday' THEN 5
                    WHEN 'Tuesday' THEN 4
                    WHEN 'Wednesday' THEN 3
                    WHEN 'Thursday' THEN 2
                    WHEN 'Friday' THEN 1
                    WHEN 'Saturday' THEN 0
                  END,
      @FirstNum = CASE DATENAME(weekday, @StartDate)
                    WHEN 'Sunday' THEN 5
                    WHEN 'Monday' THEN 4
                    WHEN 'Tuesday' THEN 3
                    WHEN 'Wednesday' THEN 2
                    WHEN 'Thursday' THEN 1
                    WHEN 'Friday' THEN 0
                    WHEN 'Saturday' THEN 0
                  END
     IF (@TotalDays < @FirstPart)
        BEGIN
          SELECT @WorkDays = @TotalDays
        END
     ELSE
        BEGIN
          SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
          SELECT @LastPart = (@TotalDays - @FirstPart) % 7
          SELECT @LastNum = CASE
            WHEN (@LastPart <> 0) THEN @LastPart - 1
            ELSE 0
          END
          SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
        END
     RETURN ( @WorkDays )
    END
    GO

    Example

    Returns the number of working days between '11/13/2000' and '12/27/2000':

    SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')

    Here is the result set:

    -----------
    31

    (1 row(s) affected)

    AddWorkingDays

    Works like DATEADD, but adds the working days.

    Syntax

    AddWorkingDays ( StartDate, WorkDays )

    Arguments

    StartDate - the datetime value (start date).
    WorkDays - the integer value (number of working days).

    Return Types

    datetime

    The function's text:

    CREATE FUNCTION dbo.ADDWorkingDays
     ( @StartDate datetime,
       @WorkDays int )
    RETURNS datetime
    AS
    BEGIN
     DECLARE @TotalDays int, @FirstPart int
     DECLARE @EndDate datetime
     DECLARE @LastNum int, @LastPart int

     IF @WorkDays < 0
        BEGIN
          SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
                    WHEN 'Sunday' THEN 0
                    WHEN 'Monday' THEN 1
                    WHEN 'Tuesday' THEN 2
                    WHEN 'Wednesday' THEN 3
                    WHEN 'Thursday' THEN 4
                    WHEN 'Friday' THEN 5
                    WHEN 'Saturday' THEN 6
                  END
          IF ABS(@WorkDays) < @FirstPart
             SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
          ELSE
            BEGIN
              SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5
              SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7
              SELECT @LastNum = CASE
                WHEN (@LastPart <> 0) THEN @LastPart - 1
                ELSE 0
              END
              SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays
              SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
            END
        END

     ELSE

        BEGIN
          SELECT @FirstPart = CASE DATENAME(weekday, @StartDate)
                    WHEN 'Sunday' THEN 6
                    WHEN 'Monday' THEN 5
                    WHEN 'Tuesday' THEN 4
                    WHEN 'Wednesday' THEN 3
                    WHEN 'Thursday' THEN 2
                    WHEN 'Friday' THEN 1
                    WHEN 'Saturday' THEN 0
                  END
          IF @WorkDays < @FirstPart
             SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate)
          ELSE
            BEGIN
              SELECT @TotalDays = (@WorkDays - @FirstPart) / 5
              SELECT @LastPart = (@WorkDays - @FirstPart) % 7
              SELECT @LastNum = CASE
                WHEN (@LastPart <> 0) THEN @LastPart - 1
                ELSE 0
              END
              SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays
              SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate)
            END
        END

     RETURN ( @EndDate )

    END
    GO

    Example

    Adds 9 working days to '11/13/2002' and returns the result date:

    SELECT dbo.AddWorkingDays ('11/13/2002', 9)

    Here is the result set:

    -------------------------------------
    2002-11-26 00:00:00.000

    (1 row(s) affected)

    FirstMonthDay

    Returns the first day of the month for the given date.

    Syntax

    FirstMonthDay ( date )

    Arguments

    date - datetime value.

    Return Types

    datetime

    The function's text:

    CREATE FUNCTION dbo.FirstMonthDay
     ( @Date datetime )
    RETURNS datetime
    AS
    BEGIN
    RETURN (CAST(STR(MONTH(@Date)) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime))
    END
    GO

    Example

    Returns the first day for the '06/15/99' date:

    SELECT dbo.FirstMonthDay('06/15/99')

    Here is the result set (from my machine):

    -------------------------------------
    1999-06-01 00:00:00.000

    (1 row(s) affected)

    LastMonthDay

    Returns the last day of the month for the given date.

    Syntax

    LastMonthDay ( date )

    Arguments

    date - datetime value.

    Return Types

    datetime

    The function's text:

    CREATE FUNCTION dbo.LastMonthDay
     ( @Date datetime )
    RETURNS datetime
    AS
    BEGIN
    RETURN (CASE WHEN MONTH(@Date)= 12
    THEN DATEADD(day,-1,CAST('01/01/'+STR(YEAR(@Date)+1) AS DateTime))
    ELSE DATEADD(day,-1,CAST(STR(MONTH(@Date)+1)+'/01/'+STR(YEAR(@Date)) AS DateTime))
    END)
    END
    GO

    Example

    Returns the last day for the '06/15/99' date:

    SELECT dbo.LastMonthDay('06/15/99')

    Here is the result set (from my machine):

    -------------------------------------
    1999-06-30 00:00:00.000

    (1 row(s) affected)
    Posted by at
    Labels:

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)




    Google