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
 

 

Operation on current date

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Jan 20, 2012 7:47 pm    Post subject: Operation on current date
Reply with quote

I have an SQL unload card in whcih one of the where condition is as below :
Code:

WHERE DATE(A.CREATED_TS) BETWEEN LAST_DAY(CURRENT_DATE - 2 MONTH) + 1 DAY AND LAST_DAY(CURRENT_DATE - 1 MONTH)


Due to certain requirements, We are trying to create this SQL card dynamically so that real values are populated in above where condition as below :
Code:

WHERE (A.CREATED_TS BETWEEN '2011-12-01-00.00.00.0000' AND '2011-12-31-23.59.59.999999')         


So basically, what I am trying to do is get the below DB2 querries implemented using DFSORT -
Code:

SELECT CHAR(LAST_DAY(CURRENT_DATE - 2 MONTH) + 1 DAY) || '-00.00.00.000000'                       
  FROM SYSIBM.SYSDUMMY1;


&

Code:

SELECT CHAR(LAST_DAY(CURRENT_DATE - 1 MONTH)) || '-23.59.59.999999'                       
  FROM SYSIBM.SYSDUMMY1;


Please advice on how to get this done.

Thanks a lot !
Back to top
View user's profile Send private message

techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Jan 20, 2012 7:58 pm    Post subject:
Reply with quote

In other words,

I have an input file(LRECl=80 & FB) with SQL querries data like below --
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
   WHERE DATE(A.CREATED_TS) BETWEEN
         LAST_DAY(CURRENT_DATE - 2 MONTH) + 1 DAY
     AND LAST_DAY(CURRENT_DATE - 1 MONTH)


And I am looking for an output file(LRECl=80 & FB) as below -
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
   WHERE DATE(A.CREATED_TS) BETWEEN
         '2011-12-01-00.00.00.0000'
     AND '2011-12-31-23.59.59.999999'
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Fri Jan 20, 2012 8:48 pm    Post subject:
Reply with quote

If I understand your requirement properly you can create the SQL through an SQL with little bit of formatting using SORT.

Please explain for more replies.
Back to top
View user's profile Send private message
techslam

New User


Joined: 03 Dec 2010
Posts: 87
Location: India

PostPosted: Fri Jan 20, 2012 9:25 pm    Post subject: @prem
Reply with quote

I need the above requirement to be performed using DFSORT only !
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Fri Jan 20, 2012 9:26 pm    Post subject:
Reply with quote

I was saying something like this not sure if this what you asked for something below but I was able to achieve it in SQL but you need to formart using sort if required

Code:

SELECT
  'SELECT * FROM TABLE A WHERE DATE(A.CREATED_TS) BETWEEN ' , ''''||
   CHAR(LAST_DAY(CURRENT_DATE - 2 MONTH) + 1 DAY) ||
  '-00.00.00.000000''' , ' AND '||''''||
  CHAR(LAST_DAY(CURRENT_DATE - 1 MONTH))||'-23.59.59.999999'||''''
FROM SYSIBM.SYSDUMMY1#


OUTPUT
Code:

SELECT * FROM TABLE A WHERE DATE(A.CREATED_TS) BETWEEN   '2011-12-01-00.00.00.000000'   AND '2011-12-31-23.59.59.999999'


The Output here comes in one single record
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Fri Jan 20, 2012 9:30 pm    Post subject: Re: @prem
Reply with quote

techslam wrote:
I need the above requirement to be performed using DFSORT only !


Wonder why icon_eek.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jan 20, 2012 10:32 pm    Post subject: Reply to: Operation on current date
Reply with quote

techslam,

Something doesn't add up. If you are only validating the date part of CREATED_TS why are you generating the control cards in timestamp format? The date validation is done only on the first 10 bytes. Why do you need to create a 26 byte timestamp string to compare?

With PTF UK90025 for z/OS DFSORT V1R10 and PTF UK90026 for z/OS DFSORT V1R12(Oct, 2010), DFSORT now supports last day of week, month, quarter and year functions.

If you want to generate the last month's beginning and end timestamps , then the following DFSORT JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD *                                                 
A                                                               
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                             
  INREC OVERLAY=(DATE2-1,C'01',1,8,Y4T,LASTDAYM,TOGREG=Y4T(-)) 
  OUTFIL BUILD=(04:C'WHERE A.CREATED_TS BETWEEN',/,             
                10:C'''',1,4,C'-',5,2,C'-',7,2,                 
                   C'-00.00.00.000000''',/,                     
                06:C'AND ''',9,10,C'-23.59.59.999999''',80:X)   
//*


The output of this is
Code:

   WHERE A.CREATED_TS BETWEEN         
         '2011-12-01-00.00.00.000000'
     AND '2011-12-31-23.59.59.999999'


For complete details of date arithmetic functions and other new functions see "User Guide for DFSORT PTFs UK90025 and UK90026" paper (sortugph.pdf) at:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000242

premkrishnan wrote:
If I understand your requirement properly you can create the SQL through an SQL with little bit of formatting using SORT.


You don't have to create the sql as you can directly generate the beginning and end timestamps in the WHERE condition itself in sql like shown below

Code:

SELECT *                                                   
  FROM Table
 WHERE CREATED_TS BETWEEN                                   
       TIMESTAMP(LAST_DAY(CURRENT DATE - 2 MONTH) + 1 DAY, 
                 TIME('00:00:00'))                         
   AND TIMESTAMP(LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY, 
                 TIME('00:00:00')) - 1 MICROSECOND         
   ; 


If you want to see what is being generated in the WHERE cond run the following SQL
Code:

SELECT TIMESTAMP(LAST_DAY(CURRENT DATE - 2 MONTH) + 1 DAY,
                 TIME('00:00:00'))                         
      ,TIMESTAMP(LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY,
                 TIME('00:00:00')) - 1 MICROSECOND         
 FROM SYSIBM.SYSDUMMY1                                     
  ;                                                       


The output is
Code:

2011-12-01-00.00.00.000000  2011-12-31-23.59.59.999999
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Fri Jan 20, 2012 10:36 pm    Post subject:
Reply with quote

Thanks Kolusu :-)
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 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