如何从 SQL Server datetime 数据类型中仅返回日期部分

如何使用示例编写一个查询，从 SQL Server datetime 数据类型中仅返回日期部分。这是经常被问到的问题之一。对于这个日期部分示例，我们将使用下面显示的数据

如何从 SQL Server datetime 数据类型中仅返回日期部分示例

在此 SQL Server 示例中，我们首先声明一个 DateTime 变量，并使用 GETDATE() 函数。接下来，我们将使用 CONVERT、CAST、DATEADD 和 DATEPART 函数从 Datetime 数据类型中仅提取日期部分。

-- 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];

如何从 datetime 数据类型中仅返回日期部分示例 2

在此示例中，我们将仅从 Hire date 列中提取日期部分。

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]