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
 

 

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: 1770
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 How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


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