4 Replies Latest reply: Nov 26, 2012 3:59 AM by niranjan RSS

    Date Format

      How to convert date format like

       

      Datecolumn

      january 21, 2011

      january 20,2010

      march 20,2005

      december 5,2012

      may 3,2011

      may 17,2012

      april 3,2006

      feb 17,2008

      october 16,2012

      july 21,2012

       

       

      into

      This type of format  21/01/2011

       

      Thanks

        • Re: Date Format
          Johannes Sunden

          One way would be this:

           

          Date((Date#(purgechar(left(Datecolumn,3) & right(Datecolumn,len(Datecolumn)-index(Datecolumn, ' ',1)),' '), 'MMMD,YYYY')),'DD/MM/YYYY') as NewDate

          • Re: Date Format
            Anand Chouhan

            Hi,

             

            You are able to make a modified date with below script also

             

            Set monthnames as

             

            Set NewMonthNames = 'january;february;march;april;may;june;july;august;september;october;november;december;january';

             

            MakeDate(Right(Datecolumn,4),  Month(Date( Date# ( Trim(Left(Datecolumn,Len(Datecolumn)-7)),'MMMM' ),'MM')), Left(Right(Datecolumn,7),2) ) as NewDate

             

            Regards,

            Anand

            • Re: Date Format

              Hi,

              you can below script for requiremnet.

               

              MonthNames:
              Mapping
              LOAD * INLINE [
                  MonthName, Value
                  january, 01
                  feb, 02
                  march, 03
                  april, 04
                  may, 05
                  june, 06
                  july, 07
                  august, 08
                  september, 09
                  october, 10
                  november, 11
                  december, 12
              ];

               

              T:
              Load
              Date(Date#(MapSubstring ('MonthNames',Date),'mm dd,yyyy'),'dd/mm/yyyy') As Date,
              Date As Date_Text
              ;
              LOAD * INLINE [
                  Date
                  "january 21,2011"
                  "january 20,2010"
                  "march 20,2005"
                  "december 5,2012"
                  "may 3,2011"
                  "may 17,2012"
                  "april 3,2006"
                  "feb 17,2008"
                  "october 16,2012"
                  "july 21,2012"
              ];

               

              I also attached the sample file. I hope it will solve your problem.

               

              Thanks,

              Niranjan M.