4 Replies Latest reply: Mar 3, 2015 11:19 AM by federico cal RSS

    "DD MMM HH:MM" TO "DD/MM/YYYY HH:MM"

      I HAVE A DATE FIELD, AND I NEED TO CONVERT IT.

       

      fOR EXAMPLE I HAVE "27 FEB 10:54" EXPECTED RESULT will be"27/02/2015 10:54"

      AND I NEED TO CONSIDER THAT IF THIS YEAR OR YEAR-1

      EXAMPLE. IF I HAVE "27 DEC 10:10" I EXPECTED RESULT will be"27/12/2014 10:10"

       

      ONLY ONE YEAR LESS.

       

      Thansk

        • Re: "DD MMM HH:MM" TO "DD/MM/YYYY HH:MM"
          saurabh singh

          hi Federica,

           

          you can use the date function to change the format like date(field,'DD/MM/YYYY HH:MM')
          but i dont see year in your example?,So how would you derive it?

           

          Regards

          Saurabh

            • Re: "DD MMM HH:MM" TO "DD/MM/YYYY HH:MM"
              Jonathan Poole

              See date() and date#() to read a specific input and create a specific output.

               

              The big question here though is how do you define if year = this year or year is last year.  Do you only have 12 months of data and any month later  than today's month counts toward last year ?  If that is the case the following should work although you won't need the 'temp' table , just load the 2nd table direct from your data source.

               

               

               

               

              temp:

              LOAD * INLINE [

                  Date

                  27 FEB 10:54

                  27 DEC 10:10

              ];

               

               

              load

                date(  date#( if( month(today()) > month(NewDate), Year(today()) - 1 , Year(Today()))  & '/' & month(NewDate) & '/' & day(NewDate) & ' ' & hour(NewDate) & ':' & Minute(NewDate), 'YYYY/MMM/D h:m') ,'DD/MM/YYYY hh:mm') as FormattedDate;

              load

                Date#( Date, 'DD MMM hh:mm') as NewDate

              Resident temp;

              • Re: "DD MMM HH:MM" TO "DD/MM/YYYY HH:MM"

                yeah i haven´t year so i need a transfomation