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.
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.