Software Development

SQL Server Group By Datetime

34 Comments

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.

34 thoughts on “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

    Reply

  2. 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. 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

  5. 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

  12. 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

  17. 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

  19. 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

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

    Reply

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

    Reply

  31. 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

Leave a Reply

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