• Subject: Re: Need SQL MonthName() function - Alternatives?
  • From: "David Morris" <dmorris@xxxxxxxxxxxxx>
  • Date: Thu, 30 Nov 2000 18:12:52 -0700

Walden,

You could use a user defined function.  You can get an example that does  
exactly? in figure 2 of the following Midrange Computing article available 
here:

http://www.midrangecomputing.com/mc/article.cfm?titleid=a289&md=20004

If you do not have a C compiler, there is enough in the article to guide you 
through the building of an RPGIV external UDF.

David Morris 

>>> WaldenL@TechSoftInc.com 11/30/00 03:58PM >>>
All,

I know there is the Month() function, but that returns 1-12 not Jan-Dec. I
want Jan-Dec. I've done the following, but I don't love the solution. Any
other ideas? Note the CASE statement in the select.

-----Begin Code-----
SELECT '<option ' || 'value = "' || trim(char(apajsg)) || '"> ' ||  
case(month(APAHDZ)) when 1 then 'Jan' when 2 then 'Feb' when 3 then 
'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'June' when 7 
then 'July' when 8 then 'Aug' when 9 then 'Sept' when 10 then 'Oct' 
when 11 then 'Nov' when 12 then 'Dec' end || ' ' ||                 
trim(char(day(apahdz))) || ' - ' || trim(APAPTX) || ' </option>'    
FROM tsapcpp WHERE apahdz >=current_date                    
-----End Code-----

but I'd prefer

-----Begin Code-----
SELECT '<option ' || 'value = "' || trim(char(apajsg)) || '"> ' || 
monthname(APAHDZ) || ' ' || trim(char(day(apahdz))) || ' - ' ||    
trim(APAPTX) || ' </option>' FROM tsapcpp WHERE apahdz>=current_date

-----End Code-----

Ideas?

-Walden

PS. In case you're wondering, here is the resultset:

String Expression                                      
<option value = "1"> Dec 4 - Dr. Frank Soltis </option>
<option value = "2"> Jan 17 - B2B Webs </option>       
<option value = "3"> Feb 21 - Database </option>       
<option value = "5"> Mar 21 - Web Facing </option>     
<option value = "6"> Apr 18 - Announcements </option>  
<option value = "7"> May 23 - Security </option>       
********  End of data  ********                        

------------
Walden H Leverich III
President
Tech Software
(516)627-3800 x11
WaldenL@TechSoftInc.com 
http://www.TechSoftInc.com 


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].