Solved

Problem getting full month name using SQL and VB.NET

Posted on 2006-11-16
11
401 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

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

Assisted Solution

by:TimCottee
TimCottee earned 175 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

18 Experts available now in Live!

Get 1:1 Help Now