How do I convert Months from Numeral to String Form in SQL Query Containing GROUP BY?

So, I’m creating a View that pulls a bunch of data (with a SUM aggregate on one field). The query contains GROUP BY clause and when I do the month conversion in the query as follows, I get an %invalid character% error.

SELECT Code, Name, Products, SUM(Costs) AS TOTAL, 
Year, to_char(to_date(PurchaseMonth),'DD-Mon-yyyy') AS MonthProductPurchased
FROM ProductCatalog
<BUNCH OF JOINS HERE>
WHERE ABC.Code=DEF.Code AND Products IS NOT null
GROUP BY Code, Name, Products, Year, MonthProductPurchased;

So, for instance, I want to convert ‘1’ in the Month field to ‘January’, ‘2’ to February, so on and so forth.

Answer

I see, you have something called PurchaseMonth and it takes the values 1, 2, 3, etc. You want to convert these to month names. How about this?

to_char(to_date('2000-' || PurchaseMonth || '-01'), 'YYYY-MM-DD'), 'MONTH')

Advertisement

Answer