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 theDATE
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
andCAST
methods are generally preferred for their simplicity and efficiency. - The
DATEADD
andDATEDIFF
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 theYourDateTimeColumn
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 calledOrders
, 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 anOrderDate
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 theYourDateTimeColumn
.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:
You can use various style codes to achieve different date formats.SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS OnlyDate; -- Output: YYYY/MM/DD
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
andCAST
are often the most efficient. - Custom formatting:
CONVERT
with style codes orFORMAT
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.
sql date t-sql