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

Possible duplicate of [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — Kenny Evitt, Oct 28 '16 at 19:38

casperOne · Accepted Answer · 2017-08-23T12:02:17.910

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.

edited Aug 23 '17 at 12:02

answered Feb 12 '09 at 19:08

casperOne

73,706
19
184
253

it's not defined as a type in my version on MS SQL Server 2005 – Slee Feb 13 '09 at 13:26
@mson: Date is not a recognized type in SQL Server 2005, as it has been pointed out in a few other responses. – casperOne Feb 13 '09 at 14:11

score 17 · Answer 2 · edited Jan 31 '11 at 09:43

17

cast (x as date)

or

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

edited Jan 31 '11 at 09:43

abatishchev

98,240
88
296
433

answered Feb 12 '09 at 19:07

mson

7,762
6
40
70

Date isn't a defined type in MS SQL Server. But year, month, day work just fine. – Mark Ransom Feb 12 '09 at 19:31
date is a defined type - check it out – mson Feb 12 '09 at 21:58
2

DATE was defined in SQL2008, not in SQL2005 or earlier versions – Kristen Feb 19 '09 at 21:00
Looks to be the cleanest syntax so far, and is also great for checking against the upper bound of a date range. – Swanny Jan 13 '15 at 23:47

score 3 · Answer 3 · answered Dec 28 '15 at 12:35

3

You can use type date with convert or cast.

convert(date, @x)

or

cast(@x as date)

answered Dec 28 '15 at 12:35

Serj-Tm

16,581
4
54
61

score 2 · Answer 4 · answered Feb 12 '09 at 19:13

2

What about this:

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

answered Feb 12 '09 at 19:13

Frederik Gheysels

56,135
11
101
154

score 1 · Answer 5 · 2009-04-03T07:00:04.357

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.

score 0 · Answer 6 · answered Feb 12 '09 at 19:12

0

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

answered Feb 12 '09 at 19:12

Mark Ransom

299,747
42
398
622

But, why use 111 (japanese) instead of 112 (iso) ? – Frederik Gheysels Feb 12 '09 at 19:17
Because ISO format 112 doesn't have punctuation, it's possible for the conversion back to datetime to guess the format wrong. I wish there was a format for yyyy-mm-dd with no time; that would be my preference. – Mark Ransom Feb 12 '09 at 19:32
Pls beware that CONVERT(VARCHAR method of removing Time is several times slower than CAST(FLOOR(CAST or DATEADD(DATEDIFF methods See also: http://stackoverflow.com/questions/133081/most-efficient-way-in-ms-sql-to-get-date-from-datetime (which I have tested similarly too) – Kristen Feb 19 '09 at 21:05
Actually, just tried SELECT CAST(CONVERT(VARCHAR(10), GetDate(), 111) AS DATETIME) here, and it gives error with my server configuration – Kristen Feb 19 '09 at 21:06

score 0 · Answer 7 · answered Feb 12 '09 at 19:13

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

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

7 Answers7

Linked

Related