Solved

Problem getting full month name using SQL and VB.NET

Posted on 2006-11-16
11
403 Views
Last Modified: 2008-02-01
Overview of Application:
I have an application, for which I want to build a calendar, where the user clicks on an event within the calendar.
My calendar will have 2 colums of month events, so January and February (for instance), will be side by side on the same line.

To implement this, I plan to use a datalist (with a two column table format) , and in each cell of the datalist I will have a datagrid / gridview which will have the events for that month.  I will have a datarelation on the month name to link the datagrid to the datalist together. All my events are stored in a datetime field in MS SQL, and I am working in VB.NET.

Issue:
I have a list of events in my database. One of them is for a hockey league. The hockey games start in September, and end in April. However, a baseball league starts in April and goes to October.

So I just want to get a list of monthnames from the beginning of the season to the end of the season. For hockey, I am looking for {September, October, November, December, January, February, March, April}, and for baseball I am looking for {April, May, June, July, August, September, October}, in that order.

My database field is "eventdate". I have tried stuff like:

SELECT     distinct DATENAME(month, MONTH(Gamedatetime)) AS Expr1
FROM         games
WHERE     (Leaguenum = 58)
ORDER BY Expr1

but this creates an error - saying I have to order it by "MONTH(gamedatetime)" - which does not sort the list in the proper way.

Help ? How can I get the monthname in the proper order (please note: I don't mind doing some of the conversion in VB if that's easier code, as long as I can get it into a dataset or list .....

0
Comment
Question by:pbissegger
  • 5
  • 4
  • 2
11 Comments
 

Author Comment

by:pbissegger
ID: 17957037
Sorry - slight error / correction, the SQL should have read ;

SELECT     distinct DATENAME(month, MONTH(eventdate)) AS Expr1
FROM         games
WHERE     (Leaguenum = 58)
ORDER BY Expr1

(same format, different table field ...)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17957038
Try this in SQL:

SELECT     DATENAME(month, MONTH(Gamedatetime))
FROM         games
WHERE     (Leaguenum = 58)
GROUP BY DATENAME(month, MONTH(Gamedatetime))
ORDER BY DATENAME(month, MONTH(Gamedatetime))
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17957045
Then try this in SQL instead ;)

SELECT     DATENAME(month, MONTH(eventdate))
FROM         games
WHERE     (Leaguenum = 58)
GROUP BY DATENAME(month, MONTH(eventdate))
ORDER BY DATENAME(month, MONTH(eventdate))
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 43

Expert Comment

by:TimCottee
ID: 17957092
Hi pbissegger,

Select Distinct DateName(Month,Month(EventDate)) As MonthName From games where leaguenum=58 order by Case When Month(EventDate)<9 Then Month(EventDate)+12 Else Month(EventDate) End

This should order it correctly.

Tim Cottee
0
 

Author Comment

by:pbissegger
ID: 17957169
I just tried it and am getting a similar error to before which is:

Error message : ORDER BY items must appear in the SELECT list if SELECT DISTINCT is specified.

Any ideas ?
0
 

Author Comment

by:pbissegger
ID: 17957177
Ahh .. I will try your 2nd post (just saw it) ....
0
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 175 total points
ID: 17957218
pbissegger,

So add it:

Select Distinct
    DateName(Month,Month(EventDate)) As MonthName,
    Case When Month(EventDate)<9 Then Month(EventDate)+12 Else Month(EventDate) End As OrderKey
From
    games
where
    leaguenum=58
order by
    Case When Month(EventDate)<9 Then Month(EventDate)+12 Else Month(EventDate) End


Tim
0
 

Author Comment

by:pbissegger
ID: 17957298
OK, here are the results so far:

Nightman (1st post), just results in one record: January
Nightman (2nd post), just results in one record: January
Tim (1st post), results in error message: Error message : ORDER BY items must appear in the SELECT list if SELECT DISTINCT is specified
Tim 2nd post, results in 7 records (using hockey league), with OrderKey ranging from 10 to 16, but Monthname is still all January


0
 
LVL 29

Accepted Solution

by:
Nightman earned 325 total points
ID: 17957414
Use this one (thanks for the ordering Tim)

SELECT
  datename(month,EventDate) as MonthName,
  Case When Month(EventDate)<9 Then Month(EventDate)+12 Else Month(EventDate) End As OrderKey
FROM
  games
where
    leaguenum=58
GROUP BY
datename(month,EventDate),
  Case When Month(EventDate)<9 Then Month(EventDate)+12 Else Month(EventDate) END
ORDER BY
  Case When Month(EventDate)<9 Then Month(EventDate)+12 Else Month(EventDate) END
0
 

Author Comment

by:pbissegger
ID: 17957458
Nightman,

That's it ! You would think that MS SQL would be a little simpler for this (what should have been easy little manouever ...)

I have increased the points to 500, and will do a bit of s split since you guys are great !
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17957471
Sorry - it's been a long day and I misread the ordering - just grouped blindly ;)

Tim had the ordering case spot on, but mixed up his grouping.

And NEITHER of use noticed the error in the datename function that you posted, and cut and pasted it verbatim.

If ever there was a case for a split ;)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question