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

 

[alansidman]

Kenny;
You will need to have two queries to get the result you want. The first one will give you a date format such as 3/2011 and the second one will change it to March/2011. If the first one is satisfactory then you don't need the second, but if you need to have it with the month spelled out, then you will need to have two queries.
Here are the two queries

SELECT Month([DateofSale]) & "/" & DatePart("yyyy",[Dateofsale]) AS [Month of Sale], [SALES DATABASE].ProductName, Sum([SALES DATABASE].QtySold) AS TotalQtySold, Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice
FROM [SALES DATABASE]
GROUP BY Month([DateofSale]) & "/" & DatePart("yyyy",[Dateofsale]), [SALES DATABASE].ProductName;
 
SELECT Format([Month of Sale],"mmmm/yyyy") AS [Month of Sale1], qSumSalesByMonth.ProductName, qSumSalesByMonth.TotalQtySold, qSumSalesByMonth.TotalSalesPrice
FROM qSumSalesByMonth;

Alan

 

[xenou]

Not sure but I think for an Access query you could simply use Format() instead of Month():

SELECT 
    [COLOR="RoyalBlue"]Format([DateOfSale],"mmm yyyy")[/COLOR] AS [Month of Sale], 
    [SALES DATABASE].ProductName, 
    Sum([SALES DATABASE].QtySold) AS TotalQtySold, 
    Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice
FROM 
    [SALES DATABASE]
GROUP BY 
    [COLOR="RoyalBlue"]Format([DateOfSale],"mmm yyyy")[/COLOR], 
    [SALES DATABASE].ProductName;

Note: my preference is formatting periods as yyyy-mm as they will sort in proper ascending or descending order by year and month.

 

[alansidman]

Xenou;
Thanks--I learn something new everyday. And I agree with you on the YYYY MMM format makes more sense.

Alan

 

[kenny9002]

Hi Alan & Xenou,

Thanks for your kind help.

@Alan - I tried the Statements you recommended but came up with following error - 'Syntax error in query expression [SALES DATABASE].ProductName
SELECT Format([Month of Sale], "mmmm/yyyy") As [Month of Sale 1]'

I wonder if there was an oversight somewhere in the statement.

@Xenou - The Statement you provided worked. However, if the MMM YYYY format is adopted, it does not order the months in ascending order e.g. Jan, Mar, Apr, May, Jun, Jul. It simply provides the result in an un-ordered manner (rather alphabetically), e.g. Apr, Jul, Jun, Mar, May.

May I know if there is an additional statement to add to the MMM YYYY format in order to Order the months in Calendar order.

Thanks to you all for your continued help.

Kenny

 

[alansidman]

Suggest you go with Xenou's option as it requires only one query as mine required two. Also add an Order By to his SQL statement just before the semi-colon

 ORDER BY [SALES DATABASE].DateOfSale;

Alan

 

[xenou]

In a grouped (aggregate) query I don't think you can include an order by clause on a field not in the result set. You could include a helper field for sorting purposes but that's ugly.

My preference would be to use end of period dates (true dates). These can be formatted in your report presentation using standard date formats built into access forms and reports.

SELECT 
    DateSerial(Year([DateOfSale]),Month([DateOfSale])+1,0) AS [Month of Sale], 
    [SALES DATABASE].ProductName, 
    Sum([SALES DATABASE].QtySold) AS TotalQtySold, 
    Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice
FROM 
    [SALES DATABASE]
GROUP BY 
    DateSerial(Year([DateOfSale]),Month([DateOfSale])+1,0), 
    [SALES DATABASE].ProductName;

I never use mm/yyyy formats anymore - following ISO 8601 guidelines are much more sensible for many reasons, and they will sort correctly even as text:
http://www.iso.org/iso/support/faqs...used_standards_other/date_and_time_format.htm

 

[james_lankford]

make a new table, call it MonthNames
give it two fields
1) MonthNumber
2) MonthName

type in the data manually
1 January
2 February
3 March
...
12 December

save your first query, the one you posted originally

then make a new query and use your first query as the source

join MonthName.MonthNumber = [Month of Sale]

show MonthName

order by [Month of Sale] or MonthNumber, doesn't matter

 

[alansidman]

Xenou;
I gave it a try and it worked for me. Here is the SQL that I used

SELECT Format([DateOfSale],"yyyy mmmm") AS [Month of Sale], [SALES DATABASE].ProductName, Sum([SALES DATABASE].QtySold) AS TotalQtySold, Sum([SALES DATABASE].TotalSales) AS TotalSalesPrice
FROM [SALES DATABASE]
GROUP BY Format([DateOfSale],"yyyy mmmm"), [SALES DATABASE].ProductName, [SALES DATABASE].DateOfSale
ORDER BY [SALES DATABASE].DateOfSale;

In the QBE, I have the display box unchecked but part of the aggregation Grouping.
Alan

 

[kenny9002]

Thanks everyone for your support. I think I may have to settle for the first Xenou's first suggestion - although I would have loved the SQL to order the months in Calender order.

@ Alan - your latest suggestion almost got what I want! If you check your test output report, it is able to order the months in Calender order BUT ends up 'ungrouping' the products, e.g. for July, it would have more than one entry for the month for the same product -
July 2011 Chenpa 20 £480.00
July 2011 Chenpa 40 £960.00

whereas, I intend it to show one entry for each product in the month as follows:
July 2011 Chenpa 60 £1,440.00

As indicated above, if it is going to be a lot of trouble achieving this, I may have to settle with the un-ordered months re Xenou's initial SQL.

Again, thanks all for your help.

Kenny