Stuck with date formatting to get Dates in common format

[#1]

Hi friends my source data is a .txt file without any separator and column names we are differentiating each column with common template.My problem is [@57:63] as [Accounting Period] contains different Date Formats as below while writing date#([@57:63] )

0201104

0201403

0201407

2011030

Need to have YYYYMM and to remove 0 Before and after YYYYMM

     [@26:27] as [AccLn],

     [@28:30] as [Prod],

     [@31:40] as [Period],

     [@41:46] as [Cert],

     [@47:56] as  [Claim],

     [@57:63] as [Accounting Period]=>Date(Date#([@57:63],'DDMMYYYY'),'YYYYMM') getting Error

Can any one please suggest to get in Accurate Format as YYYYMM

[#2]

Load

     [@1:3]  as [Rep],

     [@4:6] as [Curr],

     [@7:8] as [Org],

     [@9:10] as [Mgr],

     [@11:12] as [Maj],

     [@13:14] as [Min],

     [@15:21] as  [Iss],

     [@22:23] as   [Prd],

     [@24:25] as  [Tran],

     [@26:27] as [AccLn],

     [@28:30] as [Prod],

     [@31:40] as [Pol],

     [@41:46] as [Cert],

     [@47:56] as  [Claim],

     [@57:63] as [AccPrd],==>Date Formats differently need to get in common format of 'YYYYMM'

[@1:3]&[@57:63] as Key,

My problem is while writing Date functions on [@57:63] not getting in desired format getting errors .can any one please do needful.Thanks.

[ashfaq_haseeb]

Hi Srikant,

Try this and let me know if that works.

Load *,Date(date#(replace(trim(replace(Date, '0', ' ')), ' ', 0),'YYYYMM'),'YYYY-MM') as NewDate
      ,Date(date#(replace(trim(replace(Date, '0', ' ')), ' ', 0),'YYYYMM'),'YYYYMM') as NewDate1;
load * Inline
[
Date
0201104
0201403
0201407
2011030
];

You can format the date in a way you like.

Regards

ASHFAQ

[ashfaq_haseeb]

As per your script try this

LOAD
     [@1:3]  as [Rep],
     [@4:6] as [Curr],
     [@7:8] as [Org],
     [@9:10] as [Mgr],
     [@11:12] as [Maj],
     [@13:14] as [Min],
     [@15:21] as  [Iss],
     [@22:23] as   [Prd],
     [@24:25] as  [Tran],
     [@26:27] as [AccLn],
     [@28:30] as [Prod],
     [@31:40] as [Pol],
     [@41:46] as [Cert],
     [@47:56] as  [Claim],
     [@57:63] as [AccPrd],
     Date(date#(replace(trim(replace([@57:63], '0', ' ')), ' ', 0),'YYYYMM'),'YYYYMM') as Date,
[@1:3]&[@57:63] as Key
FROM
[My Sample.txt]
(fix, codepage is 1256);

Regards

ASHFAQ

[MarcoWedel · Accepted solution]

=Date#(Left(Num([@57:63]),6),'YYYYMM') as [Accounting Period]