- 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
