Group By Date

[Pamy]

Hi,

I would like to display the date by month using SQL statement but not sure how to write the code.
Anybody can give a piece of advise. Thks.

EG a list of dates in SQL Server
Date:
11/3/03
11/3/03
11/4/03
11/05/03

When executing the code I would like to display in the following format.

Mar
11/3/03
11/3/03

April
11/4/03

May
11/05/03

 

[wemeier]

You could include a new calculated field using the DATENAME function:

SELECT DATENAME(month, yourdatefield), yourdatefield, yourotherfields
FROM yourtablename
GROUP BY DATENAME(month, yourdatefield), yourdatefield

 

[r937]

no, that GROUP BY is illegal because it does not contain all the non-aggregate fields in the SELECT list, namely yourotherfields

grouping in the query is not required here, but DATENAME is okay

pamy, if you're using ColdFusion, what you want can be produced by the GROUP= parameter of the CFOUTPUT, but if it's something else, you will have to detect changes in the month name in your own logic


rudy