GROUP BY DateTime field

  • DECLARE @StartDate datetime,

    @EndDate datetime

    SET @StartDate = '2007-10-15'

    SET @EndDate = '2007-10-16'

    SELECT ACT.Name AS 'ActionName',

    YEAR(J.ReturnDate), MONTH(J.ReturnDate), DAY(J.ReturnDate),

    CAST(YEAR(@StartDate) AS varchar(20)) + '-' + CAST(MONTH(@StartDate) AS varchar(20)) + '-' + CAST(DAY(@StartDate) AS varchar(20)) AS 'tD',

    COUNT(ACT.Name) AS 'Captured'

    FROM JobCard J

    INNER JOIN Action A ON A.ID = J.ActionID

    INNER JOIN ProcessActionType PAT ON PAT.ID = A.ProcessActionTYpeID

    INNER JOIN ActionType ACT ON ACT.ID = PAT.ActionTYpeID

    WHERE J.ReturnDate >= @StartDate

    AND J.ReturnDate < DATEADD(day,1,@EndDate)

    GROUP BY ACT.Name, YEAR(J.ReturnDate), MONTH(J.ReturnDate), DAY(J.ReturnDate)

  • What exactly is the issue with the statement?...

    You can use convert(varchar(20),@StartDate,105 ) instead of CAST(YEAR(@StartDate) AS varchar(20)) + '-' + CAST(MONTH(@StartDate) AS varchar(20)) + '-' + CAST(DAY(@StartDate) AS varchar(20)) saves a lot of keystrokes...:)

    --Ramesh


  • Whatever you end up doing, everything in the select list (including long winded formulas) that isn't contained in an aggragate funtion, MUST BE INCLUDED IN THE GROUP BY CLAUSE. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Casting or converting will not cause performance issue on large record tables????????

    any good solution????????????

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

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