Datetime grouping question

  • I am writing a report and the users and they would like to select the grouping of the report.

    All the grouping will be on a single datetime field (oddly enough named datetime).

    They would like to be able to choose if the report is grouped by half hour, day, week, or month.

    I can accomplish day by using:

    GROUP BY CONVERT(VARCHAR(10),DateTime, 110)

    But how can I accomplish half hour, week, or month?

    Many thanks for the help.

  • Refer to the DATEPART functions in your Books Online.  There are params for returning the month or the week. 

    For the half hour - assuming you're using SQL 2000 - I would create a User function that uses a combination of the DATEPART hour & minute params to return a "half-hour id"... say 1 - 48 for a day.  Then you could use this as a grouping field.

  • Here is a "HalfHour" function I came up with:


    Returns the half hour segment of a date

    numbered 1 - 48


    CREATE  FUNCTION [dbo].[HalfHour] (@TimeIn DateTime)

    RETURNS int




     @Hour int,

     @Mins int,

     @HourPart int

     SELECT @Hour = DATEPART(hh,@TimeIn)

     SELECT @Mins = DATEPART(mi,@TimeIn)

     SELECT @HourPart =


       WHEN @Mins < 30 THEN 1

       ELSE 2


     RETURN( (@Hour*2) + @HourPart)



  • If at all possible, try to avoid giving fields the same name as a "reserved" word like "datetime" - it makes for very confusing code.  Does the stored procedure even pass the syntax check?


    Connecticut, USA

    Connecticut, USA

  • Hello Dana.

    Unfortunately at times naming conventions are out of our hands.

    In this case this database is for the Cisco VOIP phone system and we cannot change any field names.

    But to answer your question it does pass the syntax check.

    For anyone else having the same problem this is what I used for the grouping.

    For grouping by week:
    Group By DatePart(Week, [Datetime])
    For grouping by month:

    Group By DatePart(Month, [Datetime])

    For grouping by half hour:

    Group by Year([DateTime]), Month([DateTime]), Day([DateTime]), DatePart(Minute, [DateTime])/30

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply