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