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
 
Convert Date in S9 to yyyy-mm-dd

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

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Mon Oct 22, 2007 9:33 pm    Post subject: Convert Date in S9 to yyyy-mm-dd
Reply with quote

Hi,

I have a file containing date in S9(09) COMP-3 format.

Can I use some Mainframe job to produce an output file containing date in 'yyyy-mm-dd' format.

The purpose of converting to 'yyyy-mm-dd' format is to use the date in DB2 Extract Job (directly in query)

Ex: Input file (Hex ON)
Code:

00704
2012C


Output
Code:

'2007-10-24'


**PS: I know that this can be done by COBOL code. Just want to know if any tool of mainframe is capable of doing so.
Back to top
View user's profile Send private message

krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Mon Oct 22, 2007 9:45 pm    Post subject:
Reply with quote

this SORT job will do the need full, considering the position of the variable as 1.
Code:
//*******************************************************               
//STEP1    EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD i/p file
/*                                                                     
//SORTOUT  DD o/p file
//SYSIN    DD *                                                         
           SORT FIELDS=COPY                                             
           INREC OVERLAY=(p:p,5,PD,EDIT=(TTTT-TT-TT))                     
/*                                                                     

p=>starting position of your field
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Mon Oct 22, 2007 10:16 pm    Post subject: Reply to: Convert Date in S9 to yyyy-mm-dd
Reply with quote

Hi kRISPREMs,

Thank you a lot....

Its changing the date format, but i need the output in single quotes Like this
Code:

'2007-10-23'


i am at present getting like:
Code:

2007-10-23


Could you please help me in this.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Oct 23, 2007 8:51 am    Post subject:
Reply with quote

gauravgupta2808
Try this SORT JOB, this will add ' before and after the date
Code:
//*******************************************************               
//STEP1    EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD I/P FILE                                                 
//SORTOUT  DD O/P FILE                                                 
//SYSIN    DD *                                                         
       SORT FIELDS=COPY                                                 
       INREC OVERLAY=(p-1:C'''',p:p,5,PD,EDIT=(TTTT-TT-TT),C'''')       
/*                                                                     

icon_arrow.gif p-1 position may overlay the data in that position.
If it is a problem then you can use INREC BUILD=....
If need any help, provide the LRECL, position of the date field icon_question.gif
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Tue Oct 23, 2007 10:10 am    Post subject: Reply to: Convert Date in S9 to yyyy-mm-dd
Reply with quote

Prem,

I tried with a SORT card similar to your's and the output was not as expected. Actually quotes is not behaving in the way it should icon_sad.gif . Did you try testing the code? I don't think that would work. Please correct me if am wrong.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Oct 23, 2007 11:17 am    Post subject:
Reply with quote

Aaru
In my SORTIN, data(PD) starts from 3rd position.
Code:
//*******************************************************         
//STEP1    EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD *                                                 
      <                                                         
/*                                                             
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                                 
           SORT FIELDS=COPY                                     
       INREC OVERLAY=(2:C'''',3:3,5,PD,EDIT=(TTTT-TT-TT),C'''')
/*                                                             



Before posting, the code was tested.
Quote:
Actually quotes is not behaving in the way it should

Let me know how it is behaving for you icon_question.gif
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Oct 23, 2007 11:19 am    Post subject:
Reply with quote

Also the SORTOUT for my JOB is
Code:
 '2007-10-24'
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Tue Oct 23, 2007 11:44 am    Post subject: Reply to: Convert Date in S9 to yyyy-mm-dd
Reply with quote

Prem,

Quote:
Let me know how it is behaving for you


I tried with few examples.

input:

Code:
ABCD


sort condition:

Code:
INREC OVERLAY=(1:C''',2:1,4,5:C''')   


output:

Code:
',2:1,4,5:C'


condition with 2 quotes instead of one

Code:
INREC OVERLAY=(1:C'''',2:1,4,5:C'''')


output:

Code:
''BC'


I am using SYNCSORT FOR Z/OS 1.2.3.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Oct 23, 2007 11:52 am    Post subject:
Reply with quote

In general you could use
Code:
 INREC OVERLAY=(2:X'7D',3:3,5,PD,EDIT=(TTTT-TT-TT),X'7D') 
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


Joined: 31 May 2007
Posts: 31
Location: Chennai, India

PostPosted: Tue Oct 23, 2007 1:52 pm    Post subject: Reply to: Convert Date in S9 to yyyy-mm-dd
Reply with quote

Hi krisprems,

In my case the input file is LRECL 80 and the data starts at position 1.

so i made the sysin as
Code:

//SYSIN    DD *                                           
  SORT FIELDS=COPY                                       
  INREC OVERLAY=(0:C'''',1:1,5,PD,EDIT=(TTTT-TT-TT),C'''')
/*                                                       


But this is giving error:
Code:

- CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 09:19 ON TUE OCT
  SORT FIELDS=COPY                                                   
  INREC OVERLAY=(0:C'''',1:1,5,PD,EDIT=(TTTT-TT-TT),C'''')           
                 £                                                   
REFORMATTING FIELD ERROR                                             
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Oct 23, 2007 2:12 pm    Post subject:
Reply with quote

gauravgupta2808
If you use this INREC statement you will be having o/p file of length 86
Code:
INREC BUILD=(C'''',1,5,PD,EDIT=(TTTT-TT-TT),C'''',6,74)


If it is OK to truncate the data of last 6 bytes, then
Code:
INREC BUILD=(C'''',1,5,PD,EDIT=(TTTT-TT-TT),C'''',6,68)
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Oct 23, 2007 10:07 pm    Post subject:
Reply with quote

Aaru,

You need to code two apostrophes to display one apostrophe, e.g. C'''' to get '.

Code:

   INREC OVERLAY=(1:C''',2:1,4,5:C''')   


This is a single string (C'string'). Within the string, you have an apostrophe (''), then the hardcoded value (,2:1,4,5:C) and then another apostrophe (''), so you get:

Code:

',2:1,4,5:C'


Code:

  INREC OVERLAY=(1:C'''',2:1,4,5:C'''')


This result is correct:

Code:

''BC'


because of the way OVERLAY works.

You start with

ABCD

1:C'''' overlays an apostrophe in position 1 so you have

'BCD

2:1,4 overlays positions 1-4 ('BCD) in positions 2-5 so you get

''BCD

5:C'''' overlays an apostrophe in position 5 so you get

''BC'

If you used BUILD instead of OVERLAY and used the correct columns (6: instead of 5:), you'd get the correct result:

Code:

  INREC OVERLAY=(1:C'''',2:1,4,6:C'''')


gives:

'ABCD'
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Wed Oct 24, 2007 11:09 am    Post subject: Reply to: Convert Date in S9 to yyyy-mm-dd
Reply with quote

Frank,

I tried the same again and it worked. Thanks again for such a lucid explanation icon_biggrin.gif
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 Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Can EDCICONV convert EBCDIC to ASCII... natt.sut IBM Tools 3 Fri Nov 17, 2017 8:46 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

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