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

Question

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

Answer 1

cast (x as date)

or

year(x) month(x) day(x)

Answer 2

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.

Answer 3

See this link for three different versions of a date-only function. Here's the one I ended up using:

CREATE FUNCTION [dbo].[fn_GetDateOnly]  ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)

END

Answer 4

What about this:

select convert(datetime, convert(varchar(10), getdate(), 112))

Answer 5

You can also just use the plain old SQL convert function. The last argument lets you provide pre-deifined date formats, some of which remove the time. Here is the revised query.

SELECT    AutoShipItems.CustomerID, convert(nvarchar(10), AutoShipItems.NextOrderDate, 110) as 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, convert(nvarchar(10), AutoShipItems.NextOrderDate, 110) , Customer.FirstName, Customer.LastName, 
                     Customer.EmailAddress
ORDER BY AutoShipItems.NextOrderDate

Answer 6

Converting to a named string format is a pretty simple way to about this issue. Here is a detailed explanation and some samples

Answer 7

If it is all just about grouping, you can convert the datetime expression to int as well.

Simple CAST(datetime AS int) actually rounds the datetime to the nearest date rather than drops the time part. Thence,

CAST(datetime - 0.5 AS int)

Another way:

CAST(CAST(datetime AS float) AS int)

(Unlike datetimes, floats are truncated when cast to int.)

I prefer the former as it is shorter. Not sure which is better in terms of performance, though. Still, the issue may probably only arise on really big arrays of data.