从 SQL Server 中的“从日期时间"列中选择的分组中仅获取日期 [英] Get just the Date from grouping in select from DateTime column in SQL Server

问题描述

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

I need to group some records based on a date but it is a date and time field and I need to ignore the time part of is and just group by the date part - here is my SQL as it stands:

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

推荐答案

你可以这样分组:

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

我把它放到一个标量用户定义的函数中以使其更容易:

I put this into a scalar user-defined function to make it easier:

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 子句中的值，因为您现在要按不同的内容分组.

Note that you might have to change the values in the SELECT clause, since you are grouping by something different now.

这篇关于从 SQL Server 中的“从日期时间"列中选择的分组中仅获取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！