math or text

in the below sql why is year(classdate) " + " a " + " MONTH(classdate) a math command giving me 2006 - 12 = 167

and not "2006/12" as text? please help me

cmdGetCat = New SqlDataAdapter("SELECT DISTINCT year(classdate) " + " a " + " MONTH(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)

Hi,

i think the code that you provided has certain syntax problem, can you verify the codes?

anyway the sql statement should be something like this:

"SELECT DISTINCT year(classdate) + ' a ' + MONTH(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) "

|||

sorry its this.... i dont get 2006/12 ...i get 126 which is 2006-12=126 hahaha ..i need "yyyy/mm" why god why!!!

"SELECT DISTINCT year(classdate) + ' -/' + MONTH(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) "

|||

Hi,

You can use this sql statement to giv you yyyy/MM in varchar format:
SELECT CAST(YEAR(classdate) AS VARCHAR(4)) + '/' + CAST(MONTH(classdate) AS VARCHAR(2)) 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)

you can also choose to use the CONVERT function to return a certain format of the date and use the data in your programm

Hope this helps

|||

Here is a Convert Sample for you:

select convert(varchar(7),classdate,111) AS 'monthcode' FROM
...........