Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 3, 2001

Date and Time User-Defined Functions

By Alexander Chigrik


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




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















    Thanks for your registration, follow us on our social networks to keep up-to-date