Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need DATE from DB2 SYSIBM.SYSDUMMY1

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
subbu1522

New User


Joined: 11 Sep 2008
Posts: 29
Location: USA

PostPosted: Thu Feb 17, 2011 8:23 pm    Post subject: Need DATE from DB2 SYSIBM.SYSDUMMY1
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Feb 17, 2011 9:21 pm    Post subject:
Reply with quote

Code:
select cast(varchar_format(current_timestamp - 1 month,'YYMM') as char(4)) from sysibm.sysdummy1
Back to top
View user's profile Send private message
subbu1522

New User


Joined: 11 Sep 2008
Posts: 29
Location: USA

PostPosted: Thu Feb 17, 2011 9:53 pm    Post subject: Reply to: Need DATE from DB2 SYSIBM.SYSDUMMY1
Reply with quote

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
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Feb 17, 2011 10:56 pm    Post subject: Re: Need DATE from DB2 SYSIBM.SYSDUMMY1
Reply with quote

[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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1783
Location: Bloomington, IL

PostPosted: Thu Feb 17, 2011 11:00 pm    Post subject: Re: Reply to: Need DATE from DB2 SYSIBM.SYSDUMMY1
Reply with quote

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
View user's profile Send private message
subbu1522

New User


Joined: 11 Sep 2008
Posts: 29
Location: USA

PostPosted: Thu Feb 17, 2011 11:08 pm    Post subject: Reply to: Need DATE from DB2 SYSIBM.SYSDUMMY1
Reply with quote

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
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 Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us