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: