Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Convert date into required formate

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Convert date into required formate
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: 276
Location: Bang,iflex

PostPosted: Tue Apr 29, 2008 12:43 pm    Post subject:
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    Post subject:
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: 276
Location: Bang,iflex

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

Hi,

Have look at this site,

http://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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Convert date into required formate
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    Post subject:
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    Post subject:
Reply with quote

You are wrong. http://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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts convert PD to ZD ram_vizag DFSORT/ICETOOL 5 Sat Nov 05, 2016 3:06 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us