How to get Date from DateTime data type in SQL Server?

In SQL Server, there are several ways to return the date from DateTime datatype. While doing SQL development and programming, we often come across requirement to extract date part alone form columns having date and time. Here I’ve listed few of the most common and efficient ways to get the date part alone from DateTime and DateTime2 data types.Date From DateTime Datatype

 

On SQL Server 2008 and Higher

In SQL Server 2008, Microsoft introduced a new data-type “date”. This data type will store only the date part (day, month and year). You can use the date data-type along with CONVERT or CAST to extract the date part from DateTime and DateTime2.

1. Using CAST

/* Extract date part from datetime */
Declare @MyDateAndTime as datetime
Set @MyDateAndTime = '2017-12-15 10:45:56.923'

SELECT @MyDateAndTime
/* Result */
2017-12-15 10:45:56.923

SELECT CAST(@MyDateAndTime As date)
GO
/* Result */
2017-12-15

/* Extract date part from datetime2 */
Declare @MyDateAndTime2 as datetime2
Set @MyDateAndTime2 = '2017-12-16 11:15:23.2393473'

SELECT @MyDateAndTime2
/* Result */
2017-12-16 11:15:23.2393473

SELECT CAST(@MyDateAndTime2 As date)
GO
/* Result */
2017-12-16

2. Using CONVERT

/* Extract date part from datetime */
Declare @MyDateAndTime as datetime
Set @MyDateAndTime = '2017-12-15 10:45:56.923'

SELECT @MyDateAndTime
/* Result */
2017-12-15 10:45:56.923

SELECT CONVERT(date, @MyDateAndTime)
GO
/* Result */
2017-12-15

/* Extract date part from datetime2 */
Declare @MyDateAndTime2 as datetime2
Set @MyDateAndTime2 = '2017-12-16 11:15:23.2393473'
SELECT @MyDateAndTime2
/* Result */
2017-12-16 11:15:23.2393473

SELECT CONVERT(date, @MyDateAndTime2)
GO
/* Result */
2017-12-16

 

On SQL Server 2005 and Older

On SQL Server 2005 and older versions, there is no date data-type. So, we have to use some workaround to get the date part from date-time.

1. Using DATEADD and DATEDIFF

One among the common way to get date part from datetime is t use DATEADD along with DATEDIFF to remove the time part of the variable. Here is an example:

Declare @MyDateAndTime as datetime
Set @MyDateAndTime = '2017-12-15 10:45:56.923'

SELECT @MyDateAndTime
/* Result */
2017-12-15 10:45:56.923

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @MyDateAndTime))
/* Result */
2017-12-15 00:00:00.000

2. By Converting to Varchar

Another popular method is to convert the datetime to varchar with style code of 101, which will return the date part in mm/dd/yyyy format.

Declare @MyDateAndTime as datetime
Set @MyDateAndTime = '2017-12-15 10:45:56.923'

SELECT @MyDateAndTime
/* Result */
2017-12-15 10:45:56.923

SELECT CONVERT(VARCHAR(10), @MyDateAndTime, 101)
/* Result */
12/15/2017

 

Reference

  • Question on returning date from a SQL Server datetime data-type at StackOverflow.


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.