IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Convert date into required formate


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Krishnaraja

New User


Joined: 22 Apr 2007
Posts: 5
Location: Chennai

PostPosted: Tue Apr 29, 2008 12:36 pm
Reply with quote

Hi,

I have a date coloum STR_DT in the formate of YYYY-MM-DD. I need to print this in the formate MON YYYY example for 2008-04-01 - APR 2008.

Please help me to solve this. I tried with TO_CHAR and MONTHNAME both are not working.
Back to top
View user's profile Send private message
vasanthkumarhb

Active User


Joined: 06 Sep 2007
Posts: 275
Location: Bang,iflex

PostPosted: Tue Apr 29, 2008 12:43 pm
Reply with quote

HI,

Quote:
MON YYYY example for 2008-04-01 - APR 2008.



if the formatt is 2008-04-01-APR 2008

you can do it with the help of HOST VARIABLES and COBOL and formatt it and print it in console.
Back to top
View user's profile Send private message
Krishnaraja

New User


Joined: 22 Apr 2007
Posts: 5
Location: Chennai

PostPosted: Tue Apr 29, 2008 12:48 pm
Reply with quote

Hi Vasanth,

I would like to know any functions are there in DB2 for converting this date formate.

In ORACLE T0_Char function will work.

Also please give me detail explain of your solution.

Thanks
Krishna
Back to top
View user's profile Send private message
vasanthkumarhb

Active User


Joined: 06 Sep 2007
Posts: 275
Location: Bang,iflex

PostPosted: Tue Apr 29, 2008 12:57 pm
Reply with quote

Hi,

Have look at this site,

www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html

you can do it by user defined function allowed in DB2, look in to the above site gives you clear idea.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 2:33 pm
Reply with quote

try using case stmt

Code:


SELECT
CASE
WHEN MONTH(CURRENT DATE) = 1 THEN 'JAN'
WHEN MONTH(CURRENT DATE) = 2 THEN 'FEB'
....
....
....
...
ELSE NULL
END AS MONTH
FROM SYSIBM.SYSDUMMY1;



thanks,
ashimer
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Apr 29, 2008 4:41 pm
Reply with quote

The link provided give an answer to the TO_CHAR function.


Don't bother with a CASE statement, it is overworking the problem. Use the MONTHNAME (like you were attempting) function with a SUBSTRING.

SELECT SUBSTRING(MONTHNAME(STR_DT),1,3) || '-' || YEAR(STR_DT)
FROM your.table
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Apr 29, 2008 4:47 pm
Reply with quote

I stand corrected. It appears something needs to be inplace/configured at your site to be able to use MONTHNAME.
Back to top
View user's profile Send private message
srvmani
Warnings : 1

New User


Joined: 30 Dec 2003
Posts: 19

PostPosted: Tue Apr 29, 2008 5:00 pm
Reply with quote

Hi,

Please ask your administrator that in your environment is there any member they are using for the date function(like any copybook). So many organizations wont allow to use the date functions as it is in real time. They restrict use these functions.

Regards
srv
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 5:16 pm
Reply with quote

Dear stodolas,

MONTHNAME is a sample user-defined function provided by DB2. Its not a regular DB2 function.

pls Correct me if i am wrong

thanks,
ashimer
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Apr 29, 2008 5:19 pm
Reply with quote

You are wrong. publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/smonthn.htm
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 7:20 pm
Reply with quote

dear stodalas

pls chk the section definition

This section describes the sample user-defined functions that are provided with DB2 UDB for z/OS version 8.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Apr 29, 2008 7:23 pm
Reply with quote

Read the description on the section

"Sample user-defined functions

This section describes the sample user-defined functions that are provided with DB2. You can use the functions in the following ways:

* In your applications just as you would use other user-defined functions. Use the functions only if installation job DSNTEJ2U, which prepares the functions for use, has been run. Because the external programs that implement the logic of the sample functions are written in C and C++, the installation job requires that your site has IBM C/C++ for OS/390. For information on installation job DSNTEJ2U, see DB2 Installation Guide.
* As examples to help you define and implement your own user-defined functions. Data set prefix.SDSNSAMP contains the code for the sample functions.

Table 123 lists the sample user-defined functions. The detailed descriptions of the functions that follow the table include their external program names and specific names. The functions are in schema DSN8. The functions are defined to treat all string parameters, both input and output, as EBCDIC-encoded data."
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 8:02 pm
Reply with quote

i was trying to say that it is sample user-defined function provided by DB2 and needs instatllation for usage.

it is not available at all workshops by default ...

" the installation job requires that your site has IBM C/C++ for OS/390"
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Apr 29, 2008 8:07 pm
Reply with quote

which I said
[quote]
I stand corrected. It appears something needs to be inplace/configured at your site to be able to use MONTHNAME.
[/quote/
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 29, 2008 8:13 pm
Reply with quote

correct .. and i was supporting your quote ... icon_smile.gif [/img]
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Keep leading zero(s) after convert fl... SYNCSORT 7
No new posts Need help to append a date&tsp at... DFSORT/ICETOOL 9
No new posts Fetch data from programs execute (dat... DB2 3
Search our Forums:

Back to Top