Grouping date by year and month in ADP+SQL

[Edwik]

Hi all.

I have chart in ADP project/MS SQL Express and I want to see in chart table (date Field, data field) grouped by year and month: 2005.01, 2005.02, 2005.03....
I try this str = DATENAME(yyyy,date)+ DATENAME(m,date), SUM(data) FROM table GROUP BY DATENAME(yyyy,date)+ DATENAME(m,date) ORDER BY DATENAME(yyyy,date)+ DATENAME(m,date)

problem is with sorting: 2005April, 2005februar,2005,Januar - its sorting not by date, but alpabhetic.

Please, help with other grouping way!

 

[The_Doc_Man]

You are converting too soon, perhaps. But an intermediate query wouldn't kill you.

Make a query that does an ORDER BY on the RAW date field. Have it extract only what you wanted to see.

Now make a second layer of query that does NO SORTING WHATSOEVER and that creates two datepart fields from the underlying pre-sorted query.

This should do it for you.

 

[Edwik]

What You mean second layer of Query?