I have written a query and I am pretty sure that there is a shorter syntax for that.

I want to extract and format and group by the year and the month included in a datetime field.

For now, I have this:

SELECT CAST(YEAR(TB.Date) AS CHAR(4)) + '/' + REPLICATE('0',2-LEN(MONTH(TB.Date))) + CAST(Month(TB.Date) AS CHAR(2)) AS Date,

Is there a shorter way of writing it?



The complete query is:

SELECT CAST(YEAR(TB.Date) AS CHAR(4)) + '/' + REPLICATE('0',2-LEN(MONTH(TB.Date))) + CAST(Month(TB.Date) AS CHAR(2)) AS Date, 
	SH.Symbol, SH.Cusip, 	 
       SUM(TB.Shares) AS Shares, SUM(TB.Amount) AS Amount, AVG(TB.Amount / TB.Shares) AS Average
FROM TrxBuy AS TB 
INNER JOIN StockHdr AS SH 
ON SH.StockID = TB.StockID 
WHERE TB.Date BETWEEN '2001-04-01' AND '2001-04-30' 
AND TB.PortFolioID = 13 
GROUP BY CAST(YEAR(TB.Date) AS CHAR(4)) + '/' + REPLICATE('0',2-LEN(MONTH(TB.Date))) + CAST(Month(TB.Date) AS CHAR(2)), Symbol, Cusip
ORDER BY Date, Symbol