SQL Server Group By Datetime

Posted on December 6, 2005 by ajohnson

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. Greg Ostravich says:
    December 6, 2005 at 2:28 pm

    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

    Reply
  2. Rick O says:
    December 8, 2005 at 2:33 pm

    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.

    Reply
  3. Matt B says:
    December 12, 2005 at 2:40 pm

    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 :(

    Reply
  4. vikram vimal says:
    February 8, 2006 at 1:10 am

    Realy very help foul code

    Reply
  5. Matt F says:
    September 7, 2006 at 5:18 am

    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.

    Reply
  6. zahid says:
    January 9, 2007 at 10:25 pm

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

    Reply
  7. Ana says:
    January 16, 2007 at 5:53 am

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

    Reply
  8. A says:
    February 28, 2007 at 5:44 am

    Thanks a ton, you saved my life! ^^

    Reply
  9. Astrogirl says:
    March 8, 2007 at 3:30 pm

    THANK YOU SO MUCH FOR THIS CODE!

    Reply
  10. Atomaffe says:
    April 20, 2007 at 2:34 am

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

    Reply
  11. Walter says:
    May 5, 2007 at 3:00 pm

    My day has been made.

    Freaking dates.

    Reply
  12. Joe says:
    June 21, 2007 at 2:03 pm

    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

    Reply
  13. Marcos Soares says:
    August 30, 2007 at 7:14 am

    Nice tip! Thanks.

    Reply
  14. Lukas Bart says:
    February 27, 2008 at 6:59 am

    That makes my day, thanks. :)

    Reply
  15. Tim Meade says:
    March 29, 2008 at 8:18 am

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

    Reply
  16. Joe Axe says:
    September 3, 2008 at 8:08 am

    Very nice indeed… Thanks a million!

    Reply
  17. gs says:
    September 24, 2008 at 5:34 pm

    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

    Reply
  18. Fran says:
    September 26, 2008 at 3:07 am

    Just splendid. Thanks a lot!

    Reply
  19. Mathi says:
    November 25, 2008 at 10:33 pm

    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

    Reply
  20. Mathi says:
    November 25, 2008 at 10:45 pm

    Hi,

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

    Thanks,
    Mathi

    Reply
  21. Jeff says:
    November 27, 2008 at 10:36 am

    Thanks Aaron and gs!

    Reply
  22. sandeep says:
    June 16, 2009 at 3:12 am

    good job

    Reply
  23. Mark says:
    June 16, 2009 at 6:09 am

    Thanks!

    Reply
  24. Nano says:
    July 2, 2009 at 6:19 am

    Thank you for this code, Thanks a million!

    Reply
  25. Di-Jo says:
    July 10, 2009 at 2:05 am

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

    Reply
  26. beto says:
    August 24, 2009 at 7:18 pm

    Awesome… Thanks for the post.

    Reply
  27. Leonardo says:
    November 11, 2009 at 7:02 am

    Very helpful!

    Reply
  28. xutao says:
    January 29, 2011 at 7:27 pm

    Very Thanks …It’ great!

    Reply
  29. Neuron Upheaval says:
    February 24, 2011 at 9:25 am

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

    Reply
  30. someone says:
    June 10, 2011 at 3:05 am

    nice example…..

    Reply
  31. Peter Kotian says:
    July 11, 2011 at 2:28 am

    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

    Reply
  32. Mohsin says:
    February 10, 2012 at 8:12 am

    love it

    Reply
  33. Robert says:
    July 23, 2012 at 5:14 am

    perfect

    Reply
  34. shay says:
    September 11, 2012 at 6:15 am

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

    Reply

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>