grouping my month/year desending by month/year

i have some classes that I want to group by month/year (note:i dont need the day of the month)

how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so..

11/2006

12/2006

1/2007

3/2007

i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.

dbo.classgiven.classdate date of the class.thank you all

SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )

SELECT DISTINCT MONTH(dbo.classgiven.classdate)}, YEAR(dbo.classgiven.classdate) FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ORDER BY 2, 1

You will have to join or format the two columns of data in your display code.

|||

Add this ORDER BY to your SELECT statement:

ORDERBYConvert(INT,(Convert(char(4),YEAR(dbo.classgiven.classdate))+Convert(varchar,RIGHT('0'+CAST(Month(dbo.classgiven.classdate)ASvarchar),2))))

|||

is there a way to change to format of a date from 1/3/2006 to 1/2006 using a format command in sql

example fortmat (datevalue,"mm/yyyy") .

and conver does not seem to work in a sql line

|||

You will have to use something like:

MONTH(dbo.classgiven.classdate) + '/' + YEAR(dbo.classgiven.classdate)

In the SELECT statement.

|||

yes i try that then

i getDescription:An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message:BC30451: Name 'dbo' is not declared.

SELECT DISTINCT year(dbo.classgiven.classdate)" + "/" + MONTH(dbo.classgiven.classdate) AS monthcodev,MONTH(dbo.classgiven.classdate) as monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) ORDER BY 1", conNorthwind)

|||i see my mistake!! thanks it work on the money!!|||

do you know why when i use MONTH(dbo.classgiven.classdate) + '/' + YEAR(dbo.classgiven.classdate)

it does this 12-2006 as a math command and not as text

|||

Sorry! I left out Cast:

CAST(MONTH(OrderDate) AS varchar(2)) + '/' + CAST(YEAR(OrderDate) AS varchar(4))

|||thankssql