Sub-string syntax for date generation

[hargun]

hi,

I am generating a fixed width file and I need output based on the following requirement

'86' || mmddyy || '20'.


The mmddyy should correspond to the party_acct_relshp_snap_date value column as getting from source and the date is coming as 2013-11-21.

I tried the following way:

Code: Select all

'86': lnk_cpy.PARTY_ACCT_RELSHP_SNAP_DATE[3,4]:lnk_cpy.PARTY_ACCT_RELSHP_SNAP_DATE[6,7]:lnk_cpy.PARTY_ACCT_RELSHP_SNAP_DATE[9,10]:'20'

but I am getting the result like this:
8613-111-2


Please help me so that I get only mmddyy from party_acct_relshp_snap_date column.

[chulett]

You seem to think that substring syntax is [start,end] when in fact it is [start,length]. Correcting that should help.

[hargun]

i have tried by the following way to get mmddyy in between 86 and 20

Code: Select all

'86': lnk_cpy.PARTY_ACCT_RELSHP_SNAP_DATE[6,1]:lnk_cpy.PARTY_ACCT_RELSHP_SNAP_DATE[9,1]:lnk_cpy.PARTY_ACCT_RELSHP_SNAP_DATE[3,4]:'20'

the result coming like this

861213-120.

i need
8611211320.



Can you please help me to correct the position.the date is 2013-11-21

[chulett]

All of your substring lengths should be 2 based on your requirement:

mm=[6,2]
dd=[9,2]
yy=[3,2]