sql-server - 从 SQL Server 中的 DateTime 列中选择分组中仅获取日期

Question

我需要根据日期对一些记录进行分组，但它是一个日期和时间字段，我需要忽略 is 的时间部分，只按日期部分进行分组 - 这是我的 SQL:

SELECT   
    AutoShipItems.CustomerID,AutoShipItems.NextOrderDate,
    Customer.FirstName,Customer.LastName, Customer.EmailAddress
FROM        
    AutoShipItems 
        INNER JOIN    Customer ON 
            AutoShipItems.CustomerID =Customer.CustomerID
WHERE     
    (AutoShipItems.NextOrderDate <= GETDATE())
GROUP BY 
    AutoShipItems.CustomerID, AutoShipItems.NextOrderDate, 
    Customer.FirstName, Customer.LastName, 
    Customer.EmailAddress
ORDER BY 
    AutoShipItems.NextOrderDate

Answer 1

您可以按以下方式分组:

cast(floor(cast(AutoShipItems.NextOrderDate as float)) as datetime)

我将其放入标量用户定义函数中以使其更容易:

create function [dbo].[xfn_TrimTimeFromDateTime]
(
    @date as datetime
)
returns datetime with schemabinding as
begin
    --- Convert to a float, and get the integer that represents it.
    --- And then convert back to datetime.
    return cast(floor(cast(@date as float)) as datetime)
end

然后你可以这样称呼:

GROUP BY
    AutoShipItems.CustomerID, 
    dbo.xfn_TrimTimeFromDateTime(AutoShipItems.NextOrderDate), 
    Customer.FirstName, Customer.LastName, Customer.EmailAddress

请注意，您可能必须更改 SELECT 子句中的值，因为您现在按不同的内容进行分组。