Hi im using SQL2000 as a backend DB

When running this query the Month field is not being outputted

SELECT (MONTH(msg.ts)) AS 'MonthSort',
(DATENAME(mm,msg.ts) + ' ' + DATENAME(yy,msg.ts)) AS 'Month',
COUNT(msg.ts) as 'Count'
FROM msg
WHERE msg.op_id = 1
GROUP BY (DATENAME(mm,msg.ts) + ' ' + DATENAME(yy,msg.ts)),
(MONTH(msg.ts))
ORDER BY 'MonthSort' ASC


has anyone got any ideas?????

Please give a sample value of msg.ts to be able to answer.

example of msg.ts would be

'23/02/2004 15:25:12' as a datetime field in SQL2000

The problem is that SQL is looking for a datetime in the MM/DD/YYYY format however you are sending a DD/MM/YYYY format. Once you make the change your query should work.

http://www.geocities.com/scirocco_ha/DataControl.htm