2 Replies Latest reply: Apr 10, 2018 4:03 PM by THIAGO TEIXEIRA RSS

    Date transaformation

    neyeb charfedine

      Hi

      in the process of loading my data, i found out that date column was in this format " Thu Jul 09 12:33:44 WAT 2009 " and when i tried to use the function Day on that format it didn't work !

      So i was wondering if there's a format transformation function that could help me or any other solution !

        • Re: Date transaformation
          Sasidhar Parupudi

          Try like this

          Date(Date#(SubField(Replace(Date22,'WAT ',''),' ',-1)&'-'&SubField(Replace(Date22,'WAT ',''),' ',2)&'-'&SubField(Replace(Date22,'WAT ',''),' ',3),'YYYY-MMMM-DD')) As Date23

           

           

          Load

          Replace(Date22,'WAT ','') as Date22,

          Date(Date#(SubField(Replace(Date22,'WAT ',''),' ',-1)&'-'&SubField(Replace(Date22,'WAT ',''),' ',2)&'-'&SubField(Replace(Date22,'WAT ',''),' ',3),'YYYY-MMMM-DD')) As Date23

            Inline

          [Date22

          Thu Jul 09 12:33:44 WAT 2009

          ];

          • Re: Date transaformation
            THIAGO TEIXEIRA

            Another option would be this:

             

            Table:

            load

            *,

                MakeDate(Year,Month,Day) as Date_Formatted;

            load

                 Date#(SubField(replace(date_string,' ',','),',',1),'WWW') as DayName,

                 Month(Date#(SubField(replace(date_string,' ',','),',',2),'MMM')) as Month,

                 Day(Date#(SubField(replace(date_string,' ',','),',',3),'DD')) as Day,

                 SubField(replace(date_string,' ',','),',',4) as Time,

                 Year(Date#(SubField(replace(date_string,' ',','),',',6),'YYYY')) as Year;

            load * Inline [

            date_string

            " Ter Jul 09 12:33:44 WAT 2009 "

            ];