SQL Server Group By Datetime

If you’ve ever used SQL Server, you know that there’s no such thing as a Date or Time, only Datetime. According to SQL Server Books Online: “If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults to 12:00 A.M. (Midnight).”, which is all is fine and good but it becomes an issue when you want just the date or just the time from a column that stores both. Say for instance that my boss wants to see the number of orders per day over the last couple years to see which days have the most orders (‘cyber monday!‘). Unfortunately, there’s no function called date() or time() which returns just the date portion of the datetime or just the time portion of the datetime:

-- doesn't work...
SELECT date(mydate) as thedate, count(id) as perday
FROM orders
GROUP by thedate
ORDER by perday DESC

Turns out there’s a hack that does though:

SELECT distinct convert(varchar,mydate,111) as thedate, count(id) as perday
FROM orders
group by convert(varchar,mydate,111)
order by perday DESC

Hope that makes your day.

This entry was posted in Software Development. Bookmark the permalink.

34 Responses to SQL Server Group By Datetime

  1. Try DatePart – a function in your SQL statement:

    DatePart(type, date)

    Example:
    DatePart(“q”, datetimefiled) for Quarter.

    DatePart(“yyyy”, field) for year as four digit date field.

    Here are some things you can check against (types):

    yyyy year
    q Quarter
    m Month
    y Day of Year
    d Day
    w Weekday
    ww Week
    h Hour
    n minute
    s Second

    So you might have a query like this:

    Select “Valid Years are: ” & DatePart(“yyyy”, orderDate) WHERE DatePart(“yyyy”,orderDate) > 2001

    Hope this made sense.

    I think most databases support this in your SQL.

    Good luck -

    Greg

  2. Rick O says:

    I know someone out there will rant about how I’m denormalizing the data, but … I normally cheat by creating extra columns in my database:

    foo_date (date + time)
    foo_day (date + 12am)
    foo_month (date – days + 12am)
    foo_year (smallint for year)

    And, if I need it:
    foo_time (1/1/01 + time)
    foo_hour (1/1/01 + time – minutes)

    I then have a set of triggers that I cut and paste that fill in the other fields when foo_date is updated.

    Yes, that seems completely crazy … but it works. And it’s blindingly fast, as I never have to use any aggregate functions to futz with the data. (Some might argue that the data bloat leads to page hits, but RAM is cheap.) Also, the front-end logic is easier to read as you’re not building strange DatePart statements, just substituting different postfixes.

  3. Matt B says:

    I tried to look up some old queries in which I grouped by day, because I thought I found a better way to do this… then realized I’m grouping by convert(varchar,…, 101) too :(

  4. vikram vimal says:

    Realy very help foul code

  5. Matt F says:

    Nice fix Aaron. I just wanted to add that if you wan’t to be able to manipulate the output of the query as a smalldatetime, cast Aaron’s data back to a smalldatetime in your query. So effectively, you’re assigning all time portions of the data type to 00:00:00.

  6. zahid says:

    how can i run a quert in sql server to get value between two date range

  7. Ana says:

    you saved me! hehehehe… ty very much! ^^

  8. A says:

    Thanks a ton, you saved my life! ^^

  9. Astrogirl says:

    THANK YOU SO MUCH FOR THIS CODE!

  10. Atomaffe says:

    Thank you. That\’s just what i was searching for!

  11. Walter says:

    My day has been made.

    Freaking dates.

  12. Joe says:

    Nice job on this – had the issue come up at work today and this helped me complete my task. Needed number of uniqe web visitors per day. Not the lceanest code, but here it is:

    SELECT COUNT (DISTINCT(CONVERT(VARCHAR(40), sessionid))) AS ‘NumberofHits’,
    CONVERT(VARCHAR(12),LoginTime) AS ‘Date’
    FROM web_session
    WHERE ((LoginTime >= CONVERT(SMALLDATETIME,@reportBeginDate)
    AND LoginTime

  13. Marcos Soares says:

    Nice tip! Thanks.

  14. Lukas Bart says:

    That makes my day, thanks. :)

  15. Tim Meade says:

    Yep, made my day. Two hours with Google and yours was the winner! Works great.

  16. Joe Axe says:

    Very nice indeed… Thanks a million!

  17. gs says:

    The result from this query is a bit cleaner (in that it returns actual “dates” and discards the distinct construct) but unsure about the cost on the database?

    Anyway, just putting it out there …

    SELECT dateadd(dd,0, datediff(dd,0,mydate)) as thedate, count(id) as perday
    FROM orders
    group by dateadd(dd,0, datediff(dd,0,mydate))
    order by perday DESC

  18. Fran says:

    Just splendid. Thanks a lot!

  19. Mathi says:

    Hi,
    I understood the concept.But,SQL server 2005,throws
    “Column Some_Date’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”.
    Is it possible to use Date functions in Group By clause ?.Please help me to get clear Idea.

    Thanks,
    Mathi

  20. Mathi says:

    Hi,

    Sorry for previous post.It works fine while adding date convert function in selection list too.

    Thanks,
    Mathi

  21. Jeff says:

    Thanks Aaron and gs!

  22. sandeep says:

    good job

  23. Mark says:

    Thanks!

  24. Nano says:

    Thank you for this code, Thanks a million!

  25. Di-Jo says:

    SELECT YEAR(datefield) Y, MONTH(datefield) M, DAY(datefield) D FROM yourtable
    GROUP BY DAY(datefield)

  26. beto says:

    Awesome… Thanks for the post.

  27. Leonardo says:

    Very helpful!

  28. xutao says:

    Very Thanks …It’ great!

  29. Neuron Upheaval says:

    SELECT CAST(CAST(CAST(dtmStart AS FLOAT) AS INT) AS DATETIME) AS dtmStart
    FROM Class
    WHERE ClassID = 92134

  30. someone says:

    nice example…..

  31. Peter Kotian says:

    From SQL SERVER 2008 simply:
    SELECT convert(Date,DATEPLUSTIME) As OnlyDate FROM DATATABLE
    GROUP BY convert(Date,DATEPLUSTIME)

    Data typ Date is without time information

  32. Mohsin says:

    love it

  33. Robert says:

    perfect

  34. shay says:

    Great! just what I was looking for.
    Is that query can be converted to linq? (I tried to figure it out by myself …)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>