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