Binaryworld - Date and Time User-Defined Functions ... [ SQL Server -> User Defined Function ]

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 < 7) AND (@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 < 7) AND (@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 < 7) AND (@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 '0