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:
__ATLAS_TABLE_BLOCK__0__
Examples
This example returns a character string contained the date part
of the datetime value:
__ATLAS_TABLE_BLOCK__1__
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:
__ATLAS_TABLE_BLOCK__2__
Examples
This example returns a character string contained the time part
of the datetime value:
__ATLAS_TABLE_BLOCK__3__
Here is the result set:
———-
11:15AM
(1 row(s) affected)
Advertisement
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:
__ATLAS_TABLE_BLOCK__4__
Examples
Returns the number of working days between ’11/13/2000′
and ’12/27/2000′:
__ATLAS_TABLE_BLOCK__5__
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:
__ATLAS_TABLE_BLOCK__6__
Examples
Returns the first day for the ’06/15/99′ date:
__ATLAS_TABLE_BLOCK__7__
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:
__ATLAS_TABLE_BLOCK__8__
Examples
Returns the last day for the ’06/15/99′ date:
__ATLAS_TABLE_BLOCK__9__
Here is the result set (from my machine):
——————————————————
1999-06-30 00:00:00.000
(1 row(s) affected)