Date and Time User-Defined Functions



Introduction

Date and Time UDFs

  • DatePart

  • TimePart

  • GetWorkingDays

  • 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

    Is the 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

    Examples

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


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

    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

    Is the 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

    Examples

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


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

    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

    StrSeparate ( StartDate, EndDate )

    Arguments

    StartDate

    Is the datetime value (start date).

    EndDate

    Is 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 < 7) AND (@LastPart > 0) THEN @LastPart – 1
    ELSE 0
    END
    SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
    END
    RETURN ( @WorkDays )
    END
    GO

    Examples

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


    SELECT dbo.GetWorkingDays (’11/13/2000′, ’12/27/2000′)
    GO

    Here is the result set:

    ———–
    31

    (1 row(s) affected)


    FirstMonthDay


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

    Syntax

    FirstMonthDay ( date )

    Arguments

    date

    Is the 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

    Examples

    Returns the first day for the ’06/15/99′ date:


    SELECT dbo.FirstMonthDay(’06/15/99′)
    GO

    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

    Is the datetime value.

    Return Types

    datetime

    The function’s text:


    CREATE FUNCTION dbo.LastMonthDay
    ( @Date datetime )
    RETURNS datetime
    AS
    BEGIN
    RETURN (DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + ‘/’ + STR(01) + ‘/’ +
    STR(YEAR(@Date)) AS DateTime)))
    END
    GO

    Examples

    Returns the last day for the ’06/15/99′ date:


    SELECT dbo.LastMonthDay(’06/15/99′)
    GO

    Here is the result set (from my machine):

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

    (1 row(s) affected)


    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles