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
 

 

HOW TO GET PAST FIVE DATES value

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

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Wed Aug 03, 2011 7:02 pm    Post subject: HOW TO GET PAST FIVE DATES value
Reply with quote

Hi Friends,

Please help me to form a query.

How to get a past five dates value:

EX: Current date : 03AUG11

I results would be the following format

2011-08-02
2011-08-01
2011-07-31
2011-07-30
2011-07-29

is there any possible to convert this format ?
02AUG11
01AUG11
31JUL11
30JUL11
29JUL11
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Aug 03, 2011 7:13 pm    Post subject:
Reply with quote

v9 :
Code:
select varchar_format(current timestamp - 1 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 2 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 3 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 4 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 5 day,'DDMONYY') from sysibm.sysdummy1
Back to top
View user's profile Send private message
cmsmoon

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Wed Aug 03, 2011 7:22 pm    Post subject:
Reply with quote

GuyC wrote:
v9 :
Code:
select varchar_format(current timestamp - 1 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 2 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 3 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 4 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 5 day,'DDMONYY') from sysibm.sysdummy1



Thanks for your reply

I tried by it seems some error
SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME VARCHAR_FORAMT HAVING
COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
SQLSTATE = 42884 SQLSTATE RETURN CODE
SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Wed Aug 03, 2011 7:38 pm    Post subject:
Reply with quote

GuyC wrote:

varchar_format

cmsmoon wrote:

SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME VARCHAR_FORAMT HAVING
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Aug 03, 2011 7:44 pm    Post subject:
Reply with quote

RTFM sometimes stands for "read the f*ing MESSAGE"
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Wed Aug 03, 2011 9:09 pm    Post subject:
Reply with quote

GuyC wrote:
RTFM sometimes stands for "read the f*ing MESSAGE"


or : "Rolling on the floor m*******ting."

or : "Release The Flying Monkeys!"

or : "Repeat the first message"
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Aug 04, 2011 12:24 pm    Post subject:
Reply with quote

Code:


WITH TEMP1 (DATE1) AS
(
select CURRENT_DATE - 1 day  from sysibm.sysdummy1 union all
select CURRENT_DATE - 2 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 3 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 4 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 5 day from sysibm.sysdummy1
)

SELECT SUBSTR(CHAR(DATE1),9,2)||
CASE MONTH(DATE1)
WHEN 01 THEN CHAR('JAN')
WHEN 02 THEN CHAR('FEB')
WHEN 03 THEN CHAR('MAR')
WHEN 04 THEN CHAR('APR')
WHEN 05 THEN CHAR('MAY')
WHEN 06 THEN CHAR('JUN')
WHEN 07 THEN CHAR('JUL')
WHEN 08 THEN CHAR('AUG')
WHEN 09 THEN CHAR('SEP')
WHEN 10 THEN CHAR('OCT')
WHEN 11 THEN CHAR('NOV')
WHEN 12 THEN CHAR('DEC')
END ||
SUBSTR(CHAR(YEAR(DATE1)),3,2) FROM TEMP1 WITH UR;

Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Aug 04, 2011 1:23 pm    Post subject:
Reply with quote

at least use
substr('xxJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',month(date1)*3,3)
For something like that
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Aug 04, 2011 2:42 pm    Post subject:
Reply with quote

Thanks GuyC....

If will certainly reduce the complexity and number of lines.

Code:

WITH TEMP1 (DATE1) AS
(
select CURRENT_DATE - 1 day  from sysibm.sysdummy1 union all
select CURRENT_DATE - 2 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 3 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 4 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 5 day from sysibm.sysdummy1
)

SELECT SUBSTR(CHAR(DATE1),9,2)||
substr('xxJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',month(DATE1)*3,3) ||
SUBSTR(CHAR(YEAR(DATE1)),3,2) FROM TEMP1 WITH UR;
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Aug 04, 2011 3:38 pm    Post subject:
Reply with quote

Below can also be used, if number days has to be increased.

Code:
WITH FASTFIVE(DATEE,LVL) AS                         
(                                                   
SELECT CURRENT DATE - 1 day,0 FROM SYSIBM.SYSDUMMY1 
UNION ALL                                           
SELECT CURRENT DATE - (PARENT.LVL) DAY, PARENT.LVL+1
FROM FASTFIVE PARENT, SYSIBM.SYSDUMMY1 A             
WHERE PARENT.LVL+1<=5                               
)                                                   
SELECT DATEE FROM FASTFIVE                           


Thanks,
Sushanth
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 Sort Card : To get the records with ... pecsakthivel123 DFSORT/ICETOOL 7 Thu Dec 10, 2015 11:54 pm
No new posts Find list of all TSO users who used i... newroad CA Products 2 Fri Nov 06, 2015 12:01 am
No new posts difference between dates in months C... josepnass Testing & Performance analysis 6 Wed Jun 18, 2014 2:30 am
No new posts To find day of a date in the past Agni COBOL Programming 3 Sun Jun 08, 2014 8:53 pm
No new posts problem in difference of dates Kmr.deepakcs DFSORT/ICETOOL 8 Fri Oct 25, 2013 10:19 am


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