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

In this article we will show you, How to write a SQL Query to Return Date Part Only from a SQL Server Datetime datatype with example. This is one of the SQL Server Frequently Asked Question. For this example, We are going to use the below shown data

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

In this 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 SQL server Datetime Datatype.

T-SQL CODE

-- Query to Return Date Part from a Datetime datatype in SQL Server
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];

OUTPUT

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

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

T-SQL CODE

-- Query to Return Date Part from a Datetime datatype in SQL Server
USE [SQL Tutorial]
GO

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]

OUTPUT

Thank You for Visiting Our Blog