Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem getting full month name using SQL and VB.NET

Posted on 2006-11-16
11
Medium Priority
?
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 700 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 1300 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

636 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