How to Return Date Part Only from a SQL Server Datetime datatype

How to write a Query to Return Date Part Only from a SQL Server Datetime datatype with an example. It is one of the Frequently Asked Questions. For this Date Part example, We are going to use the below shown data

Employee Table 1

How to Return Date Part Only from a SQL Server Datetime datatype Example

In this SQL Server example, first, we are going to declare a DateTime variable and also use the GETDATE() function. Next, we are going to use the CONVERT, CAST, DATEADD, and DATEPART functions to extract the date part only from a Datetime Datatype.

-- Query to Return Date Part from a Datetime datatype
DECLARE @DateAdd datetime2 = '2017-05-12 14:24:04.1234567'
SELECT GETDATE() AS ToDay;

-- Using Convert without Format on Variable, and GETDATE()
SELECT CONVERT(date, GETDATE()) AS [ToDays Date 1];
SELECT CONVERT(date, @DateAdd) AS [ToDays Date 2];

-- Using Convert with Format on Variable, and GETDATE()
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [ToDays Date 3];
SELECT CONVERT(VARCHAR(10), @DateAdd, 111) AS [ToDays Date 4];

-- Using Cast Function on Variable, and GETDATE()
SELECT CAST(GETDATE() as date) AS [ToDays Date 5];
SELECT CAST(@DateAdd as date) AS [ToDays Date 6];

-- Worst case Scenario  
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AS [ToDays Date 7];
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @DateAdd)) AS [ToDays Date 8];
Return Date Part Only 2

How to Return Date Part Only from a Datetime datatype Example 2

In this example, we will extract the data part only from the Hire date column.

SELECT [FirstName] + [LastName] AS [Full Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
      ,CONVERT(date, [HireDate]) AS [HireDate 1]
      ,CONVERT(date, [HireDate], 111) AS [HireDate 2]
      ,CAST([HireDate] AS date) AS [HireDate 3]
  FROM [Employee]
How to Return Date Part Only from a Datetime datatype 3
Categories SQL