GROUP BY DateTime field

Question

GROUP BY DateTime field

karnaty

SSC Journeyman

Points: 87
More actions
October 19, 2007 at 3:52 am

#178676

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)

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

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

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

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 2

take look of this example.....

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/group-by-datetime-field-431215

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

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)

kumar ravindra Old Hand Points: 369 More actions · Answer 4

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

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