How to show Months in 3 Letters Words? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to show Months in 3 Letters Words?

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2002-07-22 : 12:10:44

Hi, I have the following SQL showing the number of quotes in each month, is it possible to show the months in 3 Letters Words

SELECT Count(Quote_ID), Month(Quote_Date)
FROM tbl_Quotes
GROUP BY Month(Quote_Date);

Current output:
23 , 1
54 , 2
76 , 3

Prefer output:
23 , Jan
54 , Feb
76 , Mar

Thanks
Matt

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-22 : 12:48:28

Select Count(Quote_ID), datepart("mmm", Quote_Date), datepart("yyyy", Quote_Date)
FROM tbl_Quotes
Group by datepart("mmm", Quote_Date), datepart("yyyy", Quote_Date)

I would suggest changing your query to should the year as well. Otherwise when you get more than a year of data, you will combine data across years.

01/15/2001, 10
01/20/2002, 20

Would result in 30 instead of 20 in your query.

Jeremy

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-22 : 13:04:21

quote:

Otherwise when you get more than a year of data, you will combine data across years.

...which, if you're looking for seasonal trends, is exactly what you'd want

matkwan
Starting Member

36 Posts

Posted - 2002-07-22 : 13:39:43

Thanks, you are right, I remember to include the year :)

Jeremy would you know why "datepart("mmm", Quote_Date)" doesn't work in Access 97 and Access 2K ?

Matt

matkwan
Starting Member

36 Posts

Posted - 2002-07-22 : 13:44:38

This question should sound stupid but I forgot how to combine 2 columns into one eg,

Select month, year etc....

I want:

Select (month + " " + year) as Date etc....

U know what I meant

Matt

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-22 : 13:49:00

When you say doesn't work, what exactly do you mean? Are you getting an error message? If so, what is the message?

quote:
...which, if you're looking for seasonal trends, is exactly what you'd want

..which, if I was looking for seasonal trends, I would probably get the count by Month and year first (like my query above), and then average the results by Month to get an annual average trend.

Thanks for pointing the seasonal thing out Arnold.

Jeremy

matkwan
Starting Member

36 Posts

Posted - 2002-07-22 : 14:03:24

When I try to execute this SQL in a Query, I get this error message "Invalid Procedure Call":

SELECT Count(tbl_Quotes.Quote_ID) AS CountOfQuote_ID, DatePart("MMM",tbl_Quotes.Quote_Date) AS Month
FROM tbl_Quotes
GROUP BY DatePart("MMM",tbl_Quotes.Quote_Date);

I think it has got to do with the "MMM", because I have tried with "M" and it works but it returns me numeric format not the 3 letters format.

I am using Access 97. (I have also tested that SQL in Access 2k too)

Matthew

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-22 : 14:15:08

Matt,

I found the same problem. Try the following and let me know how it works.

SELECT Count(tbl_Quotes.Quote_ID) AS CountOfQuote_ID, Format(tbl_Quotes.Quote_Date, "mmm") AS Month
FROM tbl_Quotes
GROUP BY Format(tbl_Quotes.Quote_Date, "mmm");

Let me know if something else doesn't work. If you want to add the Fiscal Year to this then use Format(tbl_Quotes.Quote_Date, "mmm yyyy")

Jeremy

matkwan
Starting Member

36 Posts

Posted - 2002-07-22 : 14:59:15

Thanks Jeremy that works :)

Could you please tell me why this is not sorted correctly:

SELECT Count(tbl_Quotes.Quote_ID) AS CountOfQuote_ID, Format(tbl_Quotes.Quote_Date, "mmm yyyy") AS MonthYear
FROM tbl_Quotes
GROUP BY Format(tbl_Quotes.Quote_Date, "mmm yyyy")
Order By Format(tbl_Quotes.Quote_Date, "mmm yyyy")

The output is :

CountOfQuote_ID MonthYear
1 Apr 2002
1 Jul 2002
1 Jun 2002
2 Mar 2002
1 Mar 2003
1 May 2003

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-22 : 15:11:45

It looks correct to me (if you mean to sort by the character that make up the name of the month as opposed to the calender order of the month....)

If you want to order by the calendar, you will need to add another column to your select list to order by because of the group by clause.

<O>

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-22 : 16:25:15

Matt,

Sorry I didn't get back earlier. Computer crashed.

Page47 is right. When you format the date like we did, access does a conversion to a text datatype. So April starting with an A, comes before June starting with a J. In order to get the information sorted by true month order, you should add Year(tbl_Quotes.Quote_Date), Month(tbl_Quotes.Quote_Date) to your SELECT, GROUP BY and ORDER BY clauses. This should put the information in calendar order.

Jeremy

Edited by - joldham on 07/22/2002 16:25:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources