4 Replies Latest reply: Nov 17, 2014 11:46 AM by Marco Wedel RSS

    Stuck with date formatting to get Dates in common format

      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

        • Re: Stuck with date formatting to get Dates in common format

          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.

            • Re: Re: Stuck with date formatting to get Dates in common format
              Ashfaq Mohammed

              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

            • Re: Stuck with date formatting to get Dates in common format
              Ashfaq Mohammed

              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

              • Re: Stuck with date formatting to get Dates in common format
                Marco Wedel

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