How to change dates to month names in sql

[kenny9002]

I have a Table that I want to query and group the records by Month. I have the following SQL -

SELECT (Month([DateOfSale])) AS [Month of Sale], [SALES DATABASE].ProductName, Sum([SALES DATABASE].QtySold) AS TotalQtySold, Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
FROM [SALES DATABASE]<o></o>
GROUP BY (Month([DateOfSale])), [SALES DATABASE].ProductName;


However, the above SQL gives the 'Months' in numbers, eg. 3, 4, 5, 6.
I need to add an SQL Statement that will give the Names of the Months instead of numbers and also add the year, e.g.

March 2011
April 2011
May 2011
etc.

Please help.
Thanks.

Kenny

 

[james_lankford]

...although I would have loved the SQL to order the months in Calender order.

...if it is going to be a lot of trouble achieving this, ...

I'm pretty sure my suggestion will show the month names, and will put them in the correct order and is not a lot of trouble

 

[kenny9002]

Hi James,

I have created a new table called MONTHS with two fields 'MonthNo' and 'MonthNames' as you suggested. May I know, how do I incorporate the statements that you suggested in the SQL which I originally posted.

Thanks.

Kenny

 

[xenou]

I imagine like this. But I'm not sure you can order aggregate data by a field not in the result set (?) -- try it and see.

SELECT 
	MONTHS.MonthName AS [Month of Sale], 
	[SALES DATABASE].ProductName, 
	Sum([SALES DATABASE].QtySold) AS TotalQtySold, 
	Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice
FROM 
	[SALES DATABASE]
        INNER JOIN
        MONTHS 
	    ON Month([DateOfSale]) = MONTHS.MonthNo
GROUP BY 
	MONTHS.MonthName, 
	[SALES DATABASE].ProductName
ORDER BY
        MONTHS.MonthNo
	;

Are you sure you can't implement my solution with dateserial to get true end of month dates? These will order correctly and can be formatted in your result query/form/report to display as mmm yyyy.

 

[kenny9002]

Hi Xenou,

I have tried your latest SQL. However, when I try to save it, I get the following message "Join Expression not supported".

Regarding your solution with dateserial - I was able to save it. But when I try to implement it, I get the message "Data type mismatch in criteria expression".

I am really very sorry for the troubles and all the time you all have given to helping me out. I appreciate you all.

Thanks.

Kenny

 

[xenou]

Regarding your solution with dateserial - I was able to save it. But when I try to implement it, I get the message "Data type mismatch in criteria expression".

Hmmm, Not sure. Works fine for me.
Here's a sample database demonstrating the query:
<a href="http://northernocean.net/etc/mrexcel/20110516_db2.zip">Sample Database</a>
md5 hash for the zip file: b48f6bdda1ad71c9a490019d405e6549

Maybe you have some bad dates or NULL dates?

 

[xenou]

I have tried your latest SQL. However, when I try to save it, I get the following message "Join Expression not supported".

Okay,
this implements James' solution (assume you have your table MONTHS with fields MonthNo and MonthName:
1 January
2 February
3 March
...
12 December

SELECT
    MONTHS.MonthName AS [Month of Sale],
    [SALES DATABASE].ProductName,
    SUM([SALES DATABASE].QtySold) AS TotalQtySold,
    SUM([SALES DATABASE].TotalSales) As TotalSalesPrice
FROM
    [SALES DATABASE], MONTHS
WHERE
    Month([DateOfSale]) = MONTHS.MonthNo
Group BY
    MONTHS.MonthName,
    [SALES DATABASE].ProductName,
    MONTHS.MonthNo
Order BY
    MONTHS.MonthNo
;

Using the older join syntax seems to work here. Not that we included MonthNo in the GROUP BY clause, even though it is not in the SELECT clause (answering my previous unnecessary doubts).

 

[kenny9002]

Hi Xenou,

I am glad that at last the matter is settled. You latest SQL achieves what I desire. I must say a very big 'thanks' to you and Alan and James for your matchless patience with me on this project. I will remain grateful to you all.

I will now settle down to try and think through the rest of the project with a view to coming up with a Purchases & Sales program for our small family business.

Thank you all.

Kenny