Extracting Only the Date from a SQL Server DateTime

Understanding the Problem

A DateTime datatype in SQL Server stores both the date and time components of a value. Often, you might only need the date part. This is where the techniques below come in handy.

Solutions

Here are several methods to extract the date portion from a DateTime value:

Using CONVERT function:

• Simple and direct: Converts the DateTime value to a character string and then back to a DateTime, effectively removing the time part.

SELECT CONVERT(DATE, YourDateTimeColumn) AS OnlyDate
FROM YourTable;

• Similar to CONVERT: Casts the DateTime value directly to the DATE datatype.

SELECT CAST(YourDateTimeColumn AS DATE) AS OnlyDate
FROM YourTable;

Using DATEADD and DATEDIFF functions:

• More complex but effective: Calculates the number of days since a specific date (e.g., 1900-01-01), adds that number of days back to the same date, resulting in only the date part.

SELECT DATEADD(day, DATEDIFF(day, 0, YourDateTimeColumn), 0) AS OnlyDate
FROM YourTable;

Example

Assuming you have a table named Orders with a column OrderDate of type DateTime:

SELECT OrderID, CONVERT(DATE, OrderDate) AS OrderDateOnly
FROM Orders;

This query will return the OrderID and the date part of the OrderDate for all orders.

Key Points

• All three methods will return a DATE datatype as the result.
• The CONVERT and CAST methods are generally preferred for their simplicity and efficiency.
• The DATEADD and DATEDIFF method can be useful in specific scenarios where you need to manipulate dates in other ways.

Understanding the Code Examples

Extracting Date from a DateTime

Problem: We have a datetime value in SQL Server and want to extract only the date part.

SELECT CONVERT(DATE, YourDateTimeColumn) AS OnlyDate
FROM YourTable;

• Breakdown:

  • CONVERT(DATE, YourDateTimeColumn): This part converts the value in the YourDateTimeColumn to a DATE datatype.
  • AS OnlyDate: This part gives an alias name "OnlyDate" to the result of the conversion.
  • FROM YourTable: This specifies the table from which you're selecting data.

• Example: If you have a column named OrderDate in a table called Orders, you would write:

  SELECT CONVERT(DATE, OrderDate) AS OrderDateOnly
  FROM Orders;
  

  This will return a new column named OrderDateOnly containing only the date part of each order.

SELECT CAST(YourDateTimeColumn AS DATE) AS OnlyDate
FROM YourTable;

• Example: Using the same Orders table with an OrderDate column:

  SELECT CAST(OrderDate AS DATE) AS OrderDateOnly
  FROM Orders;
  

SELECT DATEADD(day, DATEDIFF(day, 0, YourDateTimeColumn), 0) AS OnlyDate
FROM YourTable;

• • DATEDIFF(day, 0, YourDateTimeColumn): Calculates the number of days between '1900-01-01' (represented by 0) and the YourDateTimeColumn.
  • DATEADD(day, ..., 0): Adds the calculated number of days to '1900-01-01', effectively removing the time part.
  • AS OnlyDate: Gives an alias name to the result.
  • FROM YourTable: Specifies the table.

• Example: For the Orders table:

  SELECT DATEADD(day, DATEDIFF(day, 0, OrderDate), 0) AS OrderDateOnly
  FROM Orders;
  

  This will also produce a OrderDateOnly column with just the date.

Alternative Methods to Extract Date from DateTime in SQL Server

While the CONVERT, CAST, and DATEADD/DATEDIFF methods are commonly used, here are some additional approaches:

Using CONVERT with Style Codes:

• Purpose: Provides more flexibility in formatting the date output.
• Syntax:

  SELECT CONVERT(VARCHAR(10), YourDateTimeColumn, style_code) AS OnlyDate
  FROM YourTable;
  

• Example:

  SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS OnlyDate; -- Output: YYYY/MM/DD
  

  You can use various style codes to achieve different date formats.

Using FORMAT function (SQL Server 2012 and later):

• Purpose: Offers more control over date formatting.
• Example:

  SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS OnlyDate;
  

• Purpose: Constructs a date from individual year, month, and day components.
• Syntax:

  SELECT DATEFROMPARTS(YEAR(YourDateTimeColumn), MONTH(YourDateTimeColumn), DAY(YourDateTimeColumn)) AS OnlyDate
  FROM YourTable;
  

• Example:

  SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())) AS OnlyDate;
  

Choosing the Right Method:

• Simplicity and performance: CONVERT and CAST are often the most efficient.
• Custom formatting: CONVERT with style codes or FORMAT provide more flexibility.
• Specific date manipulation: DATEFROMPARTS can be useful when working with date components individually.

Remember:

• The choice of method depends on your specific requirements, SQL Server version, and performance considerations.
• Always test your code thoroughly to ensure it produces the desired results.