Alex Rivera | Logout

Get just the Date from grouping in select from DateTime column in SQL Server

Asked 2009-02-12T19:04:06.933
11

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
sql-server sql-server-2005
Edit
Report

1 Answer

23

You can group by this:

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

Which you would then call like this:

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

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

answered 2009-02-12T19:08:12.353

Your Answer