Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting Month names - how to get month names from batch table having batches

Posted on 2009-04-18
8
Medium Priority
?
337 Views
Last Modified: 2012-05-06
I have a table  "tbl_batches"
with the following columns :-
1. fld_batch_id
2. fld_course_id
3. fld_start_date
4. fld_end_date

This table consists lot of batches in diff months
I want to get a month names having batches in a particular course
0
Comment
Question by:nissiinfotechpvtltd
  • 3
  • 2
  • 2
7 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24175109
select datename(m, fld_start_date) as startmonth, datename(m, fld_end_date) as endmonth, * from tbl_batches
0
 

Author Comment

by:nissiinfotechpvtltd
ID: 24175189
thanks for ur query, but my actual problem is month name is repeating.  below is my query.

SELECT distinct DATENAME(month,tb.fld_start_date)
 AS batchmmonth,tb.fld_start_date FROM tbl_batches tb
WHERE tb.fld_course_id = 1 AND tb.fld_bookings_closed_on > GETDATE()
 ORDER BY tb.fld_start_date

 it works well. The only problem here is the distinct keyword doesn't sounds here since I have used "ORDER BY tb.fld_start_date"...  

My result is :

May
May
June
June
June
June
July
September
September
October

But, I should get a output by avoiding the duplicates and also order by month.

0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1200 total points
ID: 24175300
SELECT DATENAME(month,tb.fld_start_date)
 AS batchmmonth
 FROM tbl_batches tb
WHERE tb.fld_course_id = 1 AND tb.fld_bookings_closed_on > GETDATE()
GROUP BY DATENAME(month,tb.fld_start_date)
 ORDER BY MIN(tb.fld_start_date)
0
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!

 
LVL 43

Expert Comment

by:pcelba
ID: 24175307
Instead of DISTINCT you may use aggregation (hope you are interested in month names only):

SELECT MAX(DATENAME(month,tb.fld_start_date))  AS batchmmonth
FROM tbl_batches tb
WHERE tb.fld_course_id = 1 AND tb.fld_bookings_closed_on > GETDATE()
 GROUP BY DATEPART(month,tb.fld_start_date)
 ORDER BY DATEPART(month,tb.fld_start_date)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24175313
If you order by ORDER BY DATEPART(month,tb.fld_start_date), it will order by the name of the month, not the order in which the month actually occurs in the calendar year.
0
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 800 total points
ID: 24175655
The question is which one of above selects is better. I would say neither one because it ignores year part of the date which could also be important.

SELECT MAX(DATENAME(month,tb.fld_start_date))  AS batchmmonth,  
   MAX(DATENAME(year,tb.fld_start_date)) AS batchmYear
FROM tbl_batches tb
WHERE tb.fld_course_id = 1 AND tb.fld_bookings_closed_on > GETDATE()
 GROUP BY DATEPART(year,tb.fld_start_date), DATEPART(month,tb.fld_start_date)
 ORDER BY DATEPART(year,tb.fld_start_date), DATEPART(month,tb.fld_start_date)

Your scenario looks simpler if the year is included:

SELECT DATENAME(month,tb.fld_start_date)  AS batchmmonth,
   DATENAME(year,tb.fld_start_date)  AS batchmYear
 FROM tbl_batches tb
WHERE tb.fld_course_id = 1 AND tb.fld_bookings_closed_on > GETDATE()
GROUP BY DATENAME(year,tb.fld_start_date), DATENAME(month,tb.fld_start_date)  
ORDER BY MIN(tb.fld_start_date)

0
 

Author Closing Comment

by:nissiinfotechpvtltd
ID: 31571777
Thanks  chapmandew and pcelba.. ur query solved my problem... Thanks a lot.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 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