Display month in 3 letter abbreviation
-
In a stored procedure to list class dates, I have the following:
case
when cl.Hours > cs.HoursPerDay and cs.HoursPerDay > 0 then left(convert(varchar,cs.StartDate,0),6) + '-' + convert(varchar,DATEPART(month,DATEADD(day,(cl.Hours/cs.HoursPerDay)-1, cs.StartDate))) + ' ' + convert(varchar,DATEPART(day,DATEADD(day,(cl.Hours/cs.HoursPerDay)-1, cs.StartDate))) + ' ' + convert(varchar,DATEPART(year,cs.StartDate))
else left(convert(varchar,cs.StartDate,0),11)
If a class is 8 hours or less, it does the else case. However, if there are more than 8 hours, it goes to the next day so there is a range date (for example Sep 4 - Sep 6. I am trying to get the second part to display the abbreviation. Right now it is displaying Sep 4 - 9 6, 9 being the month of September. Any help would be appreciated
-
Hi David
Can you provide sample data for cl.hours, cs.hoursperday, cs.startdate etc and also what you want to be returned in each case? As your expression is getting quite complex, I'm thinking that it might be worthwhile creating a few local variables to break it all down a bit.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil:
Thank you very much for your taking the time to reply. First of all, please understand that I am a novice with sql. I took over this website and all the code was written by the previous webmaster.
If you are intersted in seeing what I am talking about, please go to http://www.prezant.com. Click the training link and then click the grid that says view the complete course schedule. From this, the stored procedure is called. It is a stored procedure called spSearchClasses. As it is now, the month is not properly displaying. For example, if you go to the fourth class from the bottom of the Asbestos category (Asbestos Abatement Class I,II,III. or IV) Worker Certification - $350), you will notice that the first date displays: Aug 31-3. This was before I added the section to add the month. For a class that spans two months, I wanted to put in the second month. The new stored procedure does this, but again, it is displaying a "9" instead of "Sep" like I want it to. The new stored procedure is not on there now because it was too confusing to read. I will put it back when I figure out how to display "Sep" instead of "9". This may be too much and possibly hard to read in this context, but here is the stored procedure as it stands now:
CREATE proc spSearchClasses(
@Keyword varchar(100),
@LocationID int,
@CourseCategoryID int,
@ClassScheduleID int,
@CourseID int
) as
set nocount on
declare @Today datetime
select @Today = convert(datetime,left(convert(varchar,getdate(),0),11))
/* If @CLassScheduleID is not null then other parameters are ignored */
if @ClassScheduleID is NOT NULL BEGIN
declare @ClassID int
select @ClassID = ClassID,
@LocationID = LocationID
from tblClassSchedule
where ClassScheduleID = @ClassScheduleID
/* not really a search; just grab the indicated class */
select l.LocationID,
l.Name as LocationName,
cc.CourseCategoryID,
cc.Name as CategoryName,
co.CourseID,
co.CourseName,
co.Description as CourseDescription,
cl.ClassID,
cl.ClassName,
cl.Description as ClassDescription,
cl.Price,
cl.isNew,
cs.ClassScheduleID,
case
when cl.Hours > cs.HoursPerDay and cs.HoursPerDay > 0 then left(convert(varchar,cs.StartDate,0),6) + '-' + convert(varchar,DATEPART(day,DATEADD(day,(cl.Hours/cs.HoursPerDay)-1, cs.StartDate))) + ' ' + convert(varchar,DATEPART(year,cs.StartDate))
else left(convert(varchar,cs.StartDate,0),11)
end as DateRange,
ltrim(right(convert(varchar,cs.StartTime,0),7)) + ' - ' + ltrim(right(convert(varchar,dateadd(minute,cs.HoursPerDay*60+cs.BreakMinutes,cs.StartTime),0),7)) as StartTime
from tblLocation l,
tblCourseCategory cc,
tblCourse co,
tblClass cl,
tblClassSchedule cs
where cs.LocationID = l.LocationID
and cs.ClassID = cl.ClassID
and co.CourseID = cl.CourseID
and co.CourseCategoryID = cc.CourseCategoryID
/* search clause */
and cs.ClassID = @ClassID
and cs.LocationID = @LocationID
and cs.StartDate >= @Today
END
ELSE BEGIN
if @CourseID is NOT NULL BEGIN
/* not really a search; just grab the indicated classes within the course */
select l.LocationID,
l.Name as LocationName,
cc.CourseCategoryID,
cc.Name as CategoryName,
co.CourseID,
co.CourseName,
co.Description as CourseDescription,
cl.ClassID,
cl.ClassName,
cl.Description as ClassDescription,
cl.Price,
cl.isNew,
cs.ClassScheduleID,
case
when cl.Hours > cs.HoursPerDay and cs.HoursPerDay > 0 then left(convert(varchar,cs.StartDate,0),6) + '-' + convert(varchar,DATEPART(day,DATEADD(day,(cl.Hours/cs.HoursPerDay)-1, cs.StartDate))) + ' ' + convert(varchar,DATEPART(year,cs.StartDate))
else left(convert(varchar,cs.StartDate,0),11)
end as DateRange,
ltrim(right(convert(varchar,cs.StartTime,0),7)) + ' - ' + ltrim(right(convert(varchar,dateadd(minute,cs.HoursPerDay*60+cs.BreakMinutes,cs.StartTime),0),7)) as StartTime
from tblLocation l,
tblCourseCategory cc,
tblCourse co,
tblClass cl,
tblClassSchedule cs
where cs.LocationID = l.LocationID
and cs.ClassID = cl.ClassID
and co.CourseID = cl.CourseID
and co.CourseCategoryID = cc.CourseCategoryID
/* search clause */
and co.CourseID = @CourseID
and cs.StartDate >= @Today
END
ELSE BEGIN
if @Keyword is NOT NULL BEGIN
select @Keyword = '%' + @Keyword + '%'
/* manufacture the list of acceptable locations */
create table #Location(LocationID int)
if @LocationID is NOT NULL BEGIN
insert #Location values (@LocationID)
END
ELSE BEGIN
insert #Location select LocationID from tblLocation
END
/* manufacture the list of acceptable categories */
create table #CourseCategory(CourseCategoryID int)
if @CourseCategoryID is NOT NULL BEGIN
insert #CourseCategory values (@CourseCategoryID)
END
ELSE BEGIN
insert #CourseCategory select CourseCategoryID from tblCourseCategory
END
select l.LocationID,
l.Name as LocationName,
cc.CourseCategoryID,
cc.Name as CategoryName,
co.CourseID,
co.CourseName,
co.Description as CourseDescription,
cl.ClassID,
cl.ClassName,
cl.Description as ClassDescription,
cl.Price,
cl.isNew,
cs.ClassScheduleID,
case
when cl.Hours > cs.HoursPerDay and cs.HoursPerDay > 0 then left(convert(varchar,cs.StartDate,0),6) + '-' + convert(varchar,DATEPART(day,DATEADD(day,(cl.Hours/cs.HoursPerDay)-1, cs.StartDate))) + ' ' + convert(varchar,DATEPART(year,cs.StartDate))
else left(convert(varchar,cs.StartDate,0),11)
end as DateRange,
ltrim(right(convert(varchar,cs.StartTime,0),7)) + ' - ' + ltrim(right(convert(varchar,dateadd(minute,cs.HoursPerDay*60+cs.BreakMinutes,cs.StartTime),0),7)) as StartTime
from tblLocation l,
tblCourseCategory cc,
tblCourse co,
tblClass cl,
tblClassSchedule cs
where cs.LocationID = l.LocationID
and cs.ClassID = cl.ClassID
and co.CourseID = cl.CourseID
and co.CourseCategoryID = cc.CourseCategoryID
/* search clause */
and( cc.Name LIKE @Keyword
OR co.CourseName LIKE @Keyword
OR co.Description LIKE @Keyword
OR cl.ClassName LIKE @Keyword
OR cl.Description LIKE @Keyword
 
and l.LocationID in (select LocationID from #Location)
and cc.CourseCategoryID in (select CourseCategoryID from #CourseCategory)
and cs.StartDate >= @Today
order by l.LocationID,cc.Name,co.CourseName,cl.ClassID,cs.StartDate
END
ELSE BEGIN
/* not really a search; just grab all class for the next 6 months */
select l.LocationID,
l.Name as LocationName,
cc.CourseCategoryID,
cc.Name as CategoryName,
co.CourseID,
co.CourseName,
co.Description as CourseDescription,
cl.ClassID,
cl.ClassName,
cl.Description as ClassDescription,
cl.Price,
cl.isNew,
cs.ClassScheduleID,
case
when cl.Hours > cs.HoursPerDay and cs.HoursPerDay > 0 then left(convert(varchar,cs.StartDate,0),6) + '-' + convert(varchar,DATEPART(day,DATEADD(day,(cl.Hours/cs.HoursPerDay)-1, cs.StartDate)))
else left(convert(varchar,cs.StartDate,0),6)
end as DateRange,
ltrim(right(convert(varchar,cs.StartTime,0),7)) + ' - ' + ltrim(right(convert(varchar,dateadd(minute,cs.HoursPerDay*60+cs.BreakMinutes,cs.StartTime),0),7)) as StartTime
from tblLocation l,
tblCourseCategory cc,
tblCourse co,
tblClass cl,
tblClassSchedule cs
where cs.LocationID = l.LocationID
and cs.ClassID = cl.ClassID
and co.CourseID = cl.CourseID
and co.CourseCategoryID = cc.CourseCategoryID
/* search clause */
and cs.StartDate >= @Today
and cs.StartDate <= dateadd(m,6,@Today)
order by cc.Name,co.CourseName + cl.ClassName,l.LocationID,cl.ClassID,cs.StartDate
END
END
END
GO
I'm not sure how else to relay this info to you so I apologize for the volume here.
To get back to your original question, the start date is entered as mm/dd/yyyy. If the class is more than 8 hours, it span more than one day. So what I am trying to achieve is to get the end date to be in the same format as the start date (using the 3 letter abbreviation for the month rather than just the number).
Please let me know if there is anything else I can provide. I would be happy to email you stuff if you need. I appreciate your time.
David
-
Thanks for that! As I'm supposed to have a day job, I couldn't spend too much time wading through your stored proc - no offence
Maybe this will help though - a user defined function that will return the three-letter abbrev of a month, given an integer (1-12) as the argument. Here's the UDF:create function dbo.fn_ReturnMonth(@MonthNo int)
returns varchar(20)
as
begin
declare @ret_value char(3), @months char(36)
set @ret_value = ''
set @months = 'JanFebMarAprMayJunJulAugSepOctNovDec'
if @monthno >= 1 and @monthno <= 12
set @ret_value = substring(@months, 1+(@monthno - 1) * 3, 3)
return @ret_value
end
Once you've got this function defined in your db, you can use it as follows:
select dbo.fn_ReturnMonth([monthno]) as Mth
Eg
select dbo.fn_ReturnMonth(datepart(month,getdate())) as Mth
returns the text 'Jul'.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi David,
Do you really need all these headaches? The query given below will give you desired output if you replace getdate() function with the date you want.
select
substring(datename(month,getdate()),1,3)
Hope this helps you.
Have a gr8 day.
Nivedita Sundar.N
-
Hi Nivedita:
Thanks for the post. Where would I find the getdate() function? I don't see it in the stored procedure. Like I said, I am a novice and am probably over my head with all this. Thanks.
David
-
David
You can call the getdate() function at any time and it will return the current system datetime. It is a built-in SQL Server function and therefore does not need to be defined/declared. It does not take any arguments.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil:
OK..so I created the user defined function you gave me yesterday in the user defined function section of the db. Then, I inserted:
select dbo.fn_ReturnMonth(datepart(month,getdate())) as Mth
into the stored procedure right after:
select @Today = convert(datetime,left(convert(varchar,getdate(),0),11)) - which you can see from the huge long reply from the other day.
That seemed to shut down the whole website and I had to stop and restart it.
Am I not putting the select statement in the right place?
Thanks,
David
-
Phil:
This got me the desired result:
convert(varchar,left(DATENAME(month,DATEADD(day,cl.Hours/cs.HoursPerDay-1, cs.StartDate)),3),0)
Thanks for taking the time to look at this.
David
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply