|
|
There are different ways of getting just the date part of a DATETIME data
type. In Oracle, there is function called TRUNC that can accept a
DATE/TIME parameter and will return just the date part. Unfortunately,
there is no such function available for SQL Server. In this article three
ways of getting the date part of a DATETIME data type will be discussed.
Values with the DATETIME data type are stored internally by Microsoft SQL Server
as two 4-byte integers. The first 4 bytes store the number of days before or after
the base date, January 1, 1900. The base date is the system reference date. Values
fro DATETIME earlier than January 1, 1753, are not permitted. The other 4 bytes store
the time of day represented as the number of milliseconds after midnight.
|
First Variant
One of the possible ways of getting the date part of a DATETIME data type is by
retrieving the MONTH, DAY and YEAR part of the DATETIME data type and building
a new DATETIME variable containing just these parts as shown in the following
user-defined function.
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/' +
CAST(DAY(@pInputDate) AS VARCHAR(2)) AS DATETIME)
END
GO
Description
The user-defined function simply gets the year, month and day of the input date
parameter and concatenates them together in the "YYYY/MM/DD" format. This
date format is used because SQL Server can easily convert this to a DATETIME
data type without the confusion of determining which part is the day and which
part is the month because SQL Server does not support the "YYYY/DD/MM" format,
if ever such format exists. On the other hand, if the format used is
"MM/DD/YYYY", this function may not perform properly where the default date
format is in the "DD/MM/YYYY".
In extracting the different parts of the date, the YEAR, MONTH and DAY functions
are used. The output of these functions are integer values that's why it
has to be explicitly converted to VARCHAR data type. Otherwise, an error
of "Syntax error converting the varchar value '/' to a column of data type
int." will be encountered because SQL Server will try to convert the slash
('/') character into INT data type instead of convert the integer year, month
and day into VARCHAR data type because INT data types have a higher precedence
than VARCHAR data types.
The last step in the user-defined function after concatenating the different
parts of the date is to convert it back to DATETIME data type as specified by
the outer CAST function. This step is actually not needed since the
format of the date is in the "YYYY/MM/DD" and SQL Server can already implicitly
convert this to a DATETIME data type. Given this, the user-defined
function can be simplified as follows:
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/' +
CAST(DAY(@pInputDate) AS VARCHAR(2))
END
GO
Second Variant
Why bother manually building the output date using the 3 parts as discussed in
the first variant above when there is already a built-in function that can
perform this. The CONVERT function explicity converts an expression from
one data type to another.
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)
END
GO
Description
This variant of the user-defined function basically behaves the same way as the
first variant discussed above in the sense that it converts the input date into
"YYYY/MM/DD" format getting rid of the time. The process of converting
the input date into "YYYY/MM/DD" format involves only 1 function, the CONVERT
function, compared to the first variant which used 3 functions (YEAR, MONTH and
DAY). The "YYYY/MM/DD" format is achieved by the 111 parameter of the
CONVERT function, which is the Japanese standard format.
Similar to the first variant, the last step in the user-defined function is to
convert the VARCHAR data type back to DATETIME data type, which is achieved by
the outer CAST function. Since SQL Server can implicitly convert the
"YYYY/MM/DD" date format to a DATETIME data type which is the return type of
this function, this last step is actually optional. Given this, the
function can further be simplified as follows:
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CONVERT(VARCHAR(10), @pInputDate, 111)
END
GO
Third Variant
According to Books Online,
"Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers. The first 4 bytes store the number of days
before or after the base date, January 1, 1900. The base date is the system
reference date. Values for datetime earlier than January 1, 1753, are not
permitted. The other 4 bytes store the time of day represented as the number of
milliseconds after midnight."
The third variant in getting the date part of a DATETIME data type involves the
use of the numeric representation of the input date.
CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) AS DATETIME)
END
GO
Description
A DATETIME data type can be expressed in DECIMAL format using the CAST or
CONVERT function. The integer part of the DECIMAL format represents the
date while the decimal value represents the time. By getting rid of the
decimal value, the date part of a DATETIME data type can be extracted as
demonstrated by the third variant of the user-defined function.
-
CAST(@pInputDate AS DECIMAL(12, 5)
- The first step is to convert the input date into its DECIMAL format. It
is important to use DECIMAL(12, 5) instead of INT or DECIMAL(5, 0) because SQL
Server will round the value instead of truncating it. So, any dates with
a time of greater than 12:00 PM will be rounded to the next day because the
decimal part of the date is already 0.5.
-
FLOOR(DateInDecimal)
- The FLOOR function returns the largest integer less than or equal to the
given numeric expression. This step basically converts the DECIMAL format
of the input date into an integer value truncating any decimal value.
This is similar to using the ROUND function with the third parameter supplied.
-
CAST(DateInInteger AS DATETIME) - The last step is simply converting the
integer representation of the input date back to DATETIME data type.
|