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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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