View previous topic :: View next topic
|
Author |
Message |
Krishnaraja
New User
Joined: 22 Apr 2007 Posts: 5 Location: Chennai
|
|
|
|
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 |
|
|
vasanthkumarhb
Active User
Joined: 06 Sep 2007 Posts: 275 Location: Bang,iflex
|
|
|
|
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 |
|
|
Krishnaraja
New User
Joined: 22 Apr 2007 Posts: 5 Location: Chennai
|
|
|
|
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 |
|
|
vasanthkumarhb
Active User
Joined: 06 Sep 2007 Posts: 275 Location: Bang,iflex
|
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
I stand corrected. It appears something needs to be inplace/configured at your site to be able to use MONTHNAME. |
|
Back to top |
|
|
srvmani Warnings : 1 New User
Joined: 30 Dec 2003 Posts: 19
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
correct .. and i was supporting your quote ... [/img] |
|
Back to top |
|
|
|