Get Date Only Function

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.

1. 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.
2. 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.
3. CAST(DateInInteger AS DATETIME) - The last step is simply converting the integer representation of the input date back to DATETIME data type.