How to Precisely Get Date from DateTime SQL? [7 Methods]

Navigating through databases often involves handling different data types and formats. In SQL Server, a common requirement is to get date from a DateTime column. This process, known as extracting the SQL Server date from DateTime, allows for simplified data handling and analysis by isolating the date component. Various methods can facilitate this extraction efficiently, such as using the CONVERT(), CAST(), and FORMAT() functions among others. In this article, we will unveil multiple techniques that will guide you through effectively retrieving the date from DateTime values in SQL Server, aiding in more precise data manipulation and querying.

 

Different methods to get date from datetime SQL

1. CONVERT() Function: Use the CONVERT() function to convert the DateTime to the desired date format that excludes the time portion.
2. CAST() Function: Utilize the CAST() function to change the DateTime data type to a Date data type, removing the time part.
3. FORMAT() Function: Implement the FORMAT() function to display the DateTime as a string in a specific format that only includes the date.
4. DATEPART() Function with CONCATENATION: Extract necessary date parts like day, month, and year using DATEPART() and concatenate them to form a date string.
5. FLOOR() Function with CAST(): Utilize the FLOOR() function in conjunction with CAST() to remove the time portion of the DateTime.
6. DATEDIFF() and DATEADD() Functions: Use DATEDIFF() to get the difference in days to a base date, and then add back to the base date using DATEADD(), effectively removing the time part.
7. TRY_CONVERT() Function: Employ the TRY_CONVERT() function to safely attempt a conversion from DateTime to Date data type, ensuring that only the date part is retrieved while providing null for unsuccessful conversions, enhancing query reliability and error handling.

 

Set up Lab Environment

Below is a sample SQL script to create a table named ProductSales with some example DateTime entries. This table will be utilized to demonstrate different methods of extracting the date from the DateTime column.

CREATE TABLE ProductSales
(
    SaleID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    SaleDateTime DATETIME
);

INSERT INTO ProductSales (SaleID, ProductName, SaleDateTime)
VALUES 
(1, 'Apple', '2023-10-19 10:15:30'),
(2, 'Banana', '2023-10-19T11:30:45.123'),
(3, 'Cherry', 'Oct 20 2023 14:20:00:730PM'),
(4, 'Date', '20231020 15:45:20'),
(5, 'Elderberry', '09:00:00 Oct 21 2023');

In this example, each SaleDateTime entry is represented differently, showcasing various formats that you might encounter when dealing with DateTime values in SQL Server. Note that despite the different input formats, SQL Server will standardize the storage of these DateTime values internally.

 

1. Using CONVERT() Function

The CONVERT() function is a versatile tool in SQL Server, commonly used to get date from DateTime SQL data types. It allows for the conversion of a value from one data type to another, and it’s particularly helpful in manipulating date and time values. When dealing with DateTime values, applying the CONVERT() function enables you to extract and present the date part exclusively, streamlining the data for scenarios where only the date is relevant, effectively managing the SQL Server date from DateTime extraction process.

Here is an example of how to use the CONVERT() function with the ProductSales table to extract the date part from the SaleDateTime column:

SELECT 
    SaleID,
    ProductName,
    CONVERT(DATE, SaleDateTime) AS SaleDate
FROM 
    ProductSales;

In this query, the CONVERT() function takes two arguments: the desired data type (DATE) and the column from which to extract the date (SaleDateTime). The result, aliased as SaleDate, will display the date portion of the SaleDateTime values in the ProductSales table, allowing for easier and more focused data analysis and presentation.

 

2. Using CAST() Function

The CAST() function is a fundamental tool in SQL Server, instrumental in type conversion tasks where you need to get date from DateTime SQL columns. It is used to convert a value from one data type to another specifically tailored data type. In scenarios where we are dealing with DateTime values, applying the CAST() function enables us to meticulously extract the date part, ignoring the time portion. This functionality simplifies the SQL Server date from DateTime extraction process, focusing solely on the date information and thereby facilitating more precise data analysis and manipulation.

Let me illustrate the utilization of the CAST() function using the ProductSales table:

SELECT 
    SaleID,
    ProductName,
    CAST(SaleDateTime AS DATE) AS SaleDate
FROM 
    ProductSales;

In this query, the CAST() function transforms the SaleDateTime column values from DateTime to Date data type. By doing this, only the date part of the DateTime values is extracted and displayed in the output, achieving a streamlined and date-focused result in the SaleDate column.

 

3. Using FORMAT() Function

The FORMAT() function in SQL Server is a powerful tool that helps in formatting dates and numbers to a specific format style, which is quite beneficial when you want to get date from DateTime SQL columns. It’s extensively used to convert DateTime values into a variety of date formats, enabling users to customize how date and time values are displayed. When the aim is to extract and display only the date portion from a DateTime field, the FORMAT() function proves to be incredibly effective, simplifying the SQL Server date from DateTime extraction process.

Here’s how you can use the FORMAT() function with the ProductSales table:

SELECT 
    SaleID,
    ProductName,
    FORMAT(SaleDateTime, 'yyyy-MM-dd') AS FormattedSaleDate
FROM 
    ProductSales;

In this query, the FORMAT() function applies a specific format ('yyyy-MM-dd') to the SaleDateTime column, resulting in a more refined presentation that includes only the date portion. The output under the FormattedSaleDate column will showcase the sales dates in a consistent and easy-to-read manner, enabling clearer analysis and interpretation of the sales data.

 

4. Using DATEPART() Function with CONCATENATION

The DATEPART() function is an essential tool in SQL Server for date and time manipulation, often used to get specific components such as day, month, or year from DateTime SQL fields. It plays a crucial role in scenarios where there’s a need to extract particular parts of the date and time values for more refined data analysis and presentation. Using DATEPART(), you can selectively retrieve parts of the date, which is a powerful technique for SQL Server date from DateTime extraction.

Combining DATEPART() with concatenation allows for custom formatting and presentation of the date values. Here’s how you could do this using the ProductSales table:

SELECT 
    SaleID,
    ProductName,
    CONCAT(
        DATEPART(YEAR, SaleDateTime), '-',
        RIGHT('00' + CAST(DATEPART(MONTH, SaleDateTime) AS VARCHAR(2)), 2), '-',
        RIGHT('00' + CAST(DATEPART(DAY, SaleDateTime) AS VARCHAR(2)), 2)
    ) AS CustomFormattedDate
FROM 
    ProductSales;

In this query:

• DATEPART(YEAR, SaleDateTime) gets the year part.
• DATEPART(MONTH, SaleDateTime) gets the month part, and it’s padded with zeros to ensure a consistent format.
• DATEPART(DAY, SaleDateTime) gets the day part, and it’s also padded with zeros for consistency.

 

5. Using FLOOR() Function with CAST()

The FLOOR() function, in combination with the CAST() function, is another powerful approach to get date from DateTime SQL fields in SQL Server. The FLOOR() function is used primarily for mathematical calculations, returning the largest integer less than or equal to the specified numeric expression. When paired with DateTime manipulation, it can be utilized effectively to truncate the time part from a DateTime field, assisting in simplifying the SQL Server date from DateTime extraction process.

Here’s an illustrative example using the ProductSales table:

SELECT 
    SaleID,
    ProductName,
    CAST(CAST(FLOOR(CAST(SaleDateTime AS FLOAT)) AS DATETIME) AS DATE) AS OnlyDate
FROM 
    ProductSales;

• The inner CAST(FLOOR(CAST(SaleDateTime AS FLOAT)) AS DATETIME) part removes the time portion, as before.
• The outer CAST(... AS DATE) converts the result to a Date data type, which will display without a time portion in the output.

 

6. Using TRY_CONVERT

TRY_CONVERT() is a <a href="https://www.golinuxcloud.com/sql-functions-examples/" title="35+ SQL Functions Explained in Detail [Practical Examples]" target="_blank" rel="noopener noreferrer">function in SQL</a> Server that attempts to convert a value from one data type to another and returns null if the conversion fails. It is particularly useful when working with dates to ensure that the operations proceed without errors due to data type mismatch or incorrect date formats. When you aim to get date from DateTime SQL fields, <code>TRY_CONVERT() can be a safeguard against conversion errors, enhancing the robustness of SQL Server date from DateTime manipulations.

For instance, using the ProductSales table, here’s how TRY_CONVERT() can be employed:

SELECT 
    SaleID,
    ProductName,
    TRY_CONVERT(DATE, SaleDateTime) AS SaleDate
FROM 
    ProductSales;

In this example:

• TRY_CONVERT(DATE, SaleDateTime) attempts to convert the SaleDateTime from DateTime to Date data type.
• If the conversion is successful, it returns just the date part, assisting in extracting the date from the DateTime values in SQL Server.
• If any conversion fails (e.g., due to an incorrect format or non-DateTime value), it would return null, avoiding potential errors that might stop the query execution.

 

7. Using combination of DATEADD() and DATEDIFF()

Utilizing a combination of DATEADD() and DATEDIFF() functions is a common strategy to get date from DateTime SQL fields in SQL Server. This pairing aims to strip away the time component from a DateTime field, returning only the date portion, which is a prevalent necessity in SQL Server date from DateTime manipulations for clearer and more focused data analysis.

Example using the ProductSales table:

SELECT 
    SaleID,
    ProductName,
    DATEADD(DAY, DATEDIFF(DAY, 0, SaleDateTime), 0) AS OnlyDate
FROM 
    ProductSales;

Explanation:

• DATEDIFF(DAY, 0, SaleDateTime) calculates the difference in days between the base date (0, which corresponds to '1900-01-01') and the SaleDateTime. This returns an integer.
• DATEADD(DAY, ..., 0) then adds the calculated difference back to the base date. Since we are working with days, it effectively reconstructs the date part of SaleDateTime, ignoring the time part.

 

Summary

• Various methods exist for extracting the date from a DateTime field in SQL Server, each suitable for different requirements and scenarios.
• Functions like CONVERT(), CAST(), and TRY_CONVERT() offer straightforward conversions, enabling the extraction of date parts by altering data types or formats.
• Combining functions like DATEADD() and DATEDIFF() or using functionalities like FLOOR() with CAST() provides more complex but powerful ways to manipulate and retrieve date information effectively.
• Ensuring error resilience and enhancing the robustness of SQL queries is vital, achieved by using functions like TRY_CONVERT().

Here are resources which will help you learn more:

• CAST and CONVERT
• DATEADD
• DATEDIFF
• TRY_CONVERT