Tag Archives: Date only from DateTime

How to get Date Part only from DateTime in Sql Server

Many times we come across a scenario where we need to get Date Part only from DateTime in Sql Server. There are multiple ways of doing this, here I am listing out few of them:

1) Below approach works in Sql Server 2008 and above:

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013-07-14

2) Below approaches works in all the versions of Sql server

i) Get Date Part only from DateTime using CONVERT function
Example 1:

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) 'Date Part Only'

RESULT:
Date Part Only
--------------
20130714

Example 2:

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 'Date Part Only'

RESULT:
Date Part Only
--------------
2013/07/14

The results of the above query is of type VARCHAR, if we want the result to be of type DATETIME we can write a query like below:

Example 1:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 112)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

SELECT CONVERT(DATETIME,
			CONVERT(VARCHAR(10), GETDATE(), 111)) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

ii) Get Date Part only from DateTime using DateTime functions

From performance perspective this is the better approach instead of first converting DATETIME to VARCHAR and then VARCHAR to DATETIME.

Example 1:

SELECT DATEADD(dd, 0, 
        DATEDIFF(dd, 0, GETDATE())) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

Example 2:

DECLARE  @DatePartOnly DATETIME
SEt @DatePartOnly = DATEDIFF(DD, 0, GETDATE())
SELECT @DatePartOnly 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iii) Get Date Part only from DateTime using FLOOR and CAST functions

As we know Sql Server internally stores DATETIME as two 4-byte integers. First 4-byte stores the elapsed number days since SQL Server’s DATETIME type’s start date 19000101.The Second 4-bytes Store the Time of Day i.e. clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds.

So with above said internal storgae of the DATETIME, we can first convert the DATETIME to DECIMAL, then from decimal part ignore the fractional position and get only the integer part. Finally convert the integer to DATETIME as shown below:

SELECT CAST( -- Convert the integer to DATE
         FLOOR(-- Get largest Integer less than or equal to the decimal value
                CAST(GETDATE() AS DECIMAL(12, 5)) -- Convert DATETIME to DECIMAL
              ) 
         AS DATETIME) 'Date Part Only'

RESULT:
Date Part Only
-----------------------
2013-07-14 00:00:00.000

iv) Get Date Part only from DateTime using DATEPART and CONVERT functions

DECLARE @GETDATE AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR(4),DATEPART(YEAR, @GETDATE)) 
       + '/'+ CONVERT(VARCHAR(2),DATEPART(MONTH, @GETDATE)) 
       + '/' + CONVERT(VARCHAR(2),DATEPART(DAY, @GETDATE)) 
         'Date Part Only'

RESULT:
Date Part Only
--------------
2013/7/14

You may like to read the other popular articles on Date and Time: