Solved

Problem getting full month name using SQL and VB.NET

Posted on 2006-11-16
11
402 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

948 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now