|
|
| |
Date and Time User-Defined Functions
Alexander Chigrik
chigrik@mssqlcity.com
- 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 '06/15/99' date:
SELECT dbo.LastMonthDay('06/15/99')
Here is the result set (from my machine):
-------------------------------------
1999-06-30 00:00:00.000
(1 row(s) affected)
|
|
|
