View previous topic :: View next topic
|
Author |
Message |
nandy.paromita
New User
Joined: 04 Dec 2007 Posts: 4 Location: Chennai
|
|
|
|
Hi all,
I have a query that extracts the month (current month -1) . I need it in 'MM' format and it retrieves as 'M'.
Eg: Current month -1 = 03-01 = 02 but the query retrieves only 2.
SELECT
CHAR(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS))
FROM SYSIBM.SYSDUMMY1
WITH UR;
PLease help !
Thanks,
Nandy |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Welcome Nandy,
Check it out,
Code: |
SELECT
DIGITS(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS))
FROM SYSIBM.SYSDUMMY1
WITH UR; |
For your exact answer use SUBSTR.
Thank You,
Sushanth Bobby |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Code: |
05 LAST-MONTH PIC S9(9) COMP-5.
SELECT
MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS)
INTO :LAST-MONTH
FROM SYSIBM.SYSDUMMY1
WITH UR;
|
The CHAR function supresses leading zeros. |
|
Back to top |
|
|
nandy.paromita
New User
Joined: 04 Dec 2007 Posts: 4 Location: Chennai
|
|
|
|
Hi Sushanth and Craq,
Thank you so much for the response but still desired results are not obtained.
My complete query is:
SELECT
DEC(CONCAT(SUBSTR(CHAR(YEAR(CURRENT_DATE - DAY(CURRENT_DATE)DAYS)),1,4),
DIGITS(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS))))
FROM SYSIBM.SYSDUMMY1
WITH UR;
and the result is 20090000000002.
whereas i am expecting 200902.
Please suggest.
Thanks,
Nandy |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Try to play with functions a little-bit Nandy,
Check this out,
Code: |
SELECT CONCAT(STRIP(CHAR(YEAR(CURRENT DATE))),
SUBSTR(DIGITS(MONTH(CURRENT_DATE - DAY(CURRENT_DATE)DAYS)),9,2))
FROM SYSIBM.SYSDUMMY1; |
Sushanth |
|
Back to top |
|
|
nandy.paromita
New User
Joined: 04 Dec 2007 Posts: 4 Location: Chennai
|
|
|
|
Thank you so much Sushanth, the query is perfect ! |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Here's another method which I think would give the same results.
Code: |
SELECT SUBSTR(CHAR(CURRENT DATE - 1 MONTH),7,4) ||
SUBSTR(CHAR(CURRENT DATE - 1 MONTH),1,2)
FROM SYSIBM.SYSDUMMY1;
|
|
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Arun,
Can you re-check your query ?
Cause iam getting a different answer(2-1920) than what NANDY is expecting(200902).
Thank You,
Sushanth |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Sushanth,
The SQL which I provided is works fine for me and the solution is installation dependent. It depends on the format in which CURRENT_DATE is presented by DB2 on your system. My shop uses the IBM standard for U.S. date and time format (USA) ie. MM/DD/YYYY.
You can still convert the default format to any desired format using the CHAR function. Say for e.g. to convert it to ISO format (YYYY-MM-DD) you can simply specify CHAR(CURRENT DATE,ISO). By overriding the default format you can make this work on any system.
Code: |
SELECT SUBSTR(CHAR(CURRENT DATE - 1 MONTH,USA),7,4) ||
SUBSTR(CHAR(CURRENT DATE - 1 MONTH,USA),1,2)
FROM SYSIBM.SYSDUMMY1 |
|
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Arun,
I did'nt know that.
I think your query is more efficient than mine. I have used lots of function.
Thank You,
Sushanth Bobby |
|
Back to top |
|
|
|