|
View previous topic :: View next topic
|
| Author |
Message |
subbu1522
New User
Joined: 11 Sep 2008
Posts: 29
Location: USA
|
|
|
|
I'm in need of DB2 query getting Date (YYMM) return from DB2.
Below is my requirement:
If Current Month is JAN 2011 Value should be '1012'
If Current Month is FEB 2011 Value should be '1101'
If Current Month is MAR 2011 Value should be '1102'
If Current Month is APR 2011 Value should be '1103'
If Current Month is MAY 2011 Value should be '1104'
If Current Month is JUN 2011 Value should be '1105'
If Current Month is JUL 2011 Value should be '1106'
If Current Month is AUG 2011 Value should be '1107'
If Current Month is SEP 2011 Value should be '1108'
If Current Month is OCT 2011 Value should be '1109'
If Current Month is NOV 2011 Value should be '1110'
If Current Month is DEC 2011 Value should be '1111'
I'm currently using the below SQL query but it is getting me the current Year and Month.
| Code: |
SELECT
SUBSTR(DIGITS(YEAR(CURRENT_DATE)),9,2)||
SUBSTR(DIGITS(MONTH(CURRENT_DATE)),9,2)
FROM
SYSIBM.SYSDUMMY1;
|
Could some one please help me. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| select cast(varchar_format(current_timestamp - 1 month,'YYMM') as char(4)) from sysibm.sysdummy1 |
|
|
| Back to top |
|
|
subbu1522
New User
Joined: 11 Sep 2008
Posts: 29
Location: USA
|
|
|
|
By using the below query I'm getting an error.
| Code: |
| SELECT CAST(VARCHAR_FORMAT(CURRENT_TIMESTAMP-1 MONTH,'YYMM') AS CHAR(4)) FROM SYSIBM.SYSDUMMY1; |
Please find the below error message.
| Code: |
11:19:21 [SELECT - 0 row(s), 0.000 secs] [Error Code: -4700, SQL State: 56038] DB2 SQL error: SQLCODE: -4700, SQLSTATE: 56038, SQLERRMC: null
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
|
|
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
[quote="subbu1522"]
| Code: |
SELECT
SUBSTR(DIGITS(YEAR(CURRENT_DATE)),9,2)||
SUBSTR(DIGITS(MONTH(CURRENT_DATE)),9,2)
FROM
SYSIBM.SYSDUMMY1;
|
How about
| Code: |
SELECT
SUBSTR(DIGITS(YEAR(CURRENT_DATE - 1 MONTH)),9,2)||
SUBSTR(DIGITS(MONTH(CURRENT_DATE - 1 MONTH)),9,2)
FROM
SYSIBM.SYSDUMMY1;
|
|
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| subbu1522 wrote: |
Please find the below error message.
| Code: |
11:19:21 [SELECT - 0 row(s), 0.000 secs] [Error Code: -4700, SQL State: 56038] DB2 SQL error: SQLCODE: -4700, SQLSTATE: 56038, SQLERRMC: null
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
|
|
Please find the manual page with this error code on it, which tells you exactly what the problem is and what to do about it. |
|
| Back to top |
|
|
subbu1522
New User
Joined: 11 Sep 2008
Posts: 29
Location: USA
|
|
|
|
I have the other query which is working for the same requirement.
| Code: |
SELECT REPLACE(SUBSTR(CAST(CURRENT_DATE - 1 MONTH AS CHAR(7)),3),'-','')
FROM SYSIBM.SYSDUMMY1; |
Thanks all of you for your help! |
|
| Back to top |
|
|
|
|
