11 Replies Latest reply: Jul 13, 2015 6:40 AM by Sunny Talwar RSS

    Hours

      Hi All ,

       

      Below is my data

       

       

      SYSTEM - 01/04/2015 01:28:51 PM#
      SYSTEM - 01/04/2015 01:49:07 PM#
      SYSTEM - 01/04/2015 02:05:21 PM#
      SYSTEM - 01/04/2015 02:09:13 PM#
      SYSTEM - 01/04/2015 02:09:38 PM#
      SYSTEM - 01/04/2015 02:13:22 PM#


      In the above data , I have to take only the hour and min .

      The Time has to converted to 24 Hours Format.

       

      Need input on this

        • Re: Hours
          Sunny Talwar

          Try this:

           

          Time(TimeStamp#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 10, 22), 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm') as Time


          Tried it in a text box and got the following output

           

          Capture.PNG

          • Re: Hours
            Kranthikumar Miryala

            LOAD

            Time_Stamp,

            Timestamp(Timestamp#(Trim(Mid(Time_Stamp,20,12)),'hh:mm:ss TT'),'hh:mm ') as H24_MM;

            Temp:

            LOAD * INLINE [

                Time_Stamp

                SYSTEM - 01/04/2015 01:28:51 PM#

                SYSTEM - 01/04/2015 01:49:07 PM#

                SYSTEM - 01/04/2015 02:05:21 PM#

                SYSTEM - 01/04/2015 02:09:13 PM#

                SYSTEM - 01/04/2015 02:09:38 PM#

                SYSTEM - 01/04/2015 02:13:22 PM#

            ];

            • Re: Hours
              Settu Periyasamy

              One more..

              Load Field,
              Time(Trim(SubField(SubField(Field,'-',2),'#',1)),'HH.MM') as Hours;
              LOAD * INLINE [
                  Field
                  SYSTEM - 01/04/2015 01:28:51 PM#
                  SYSTEM - 01/04/2015 01:49:07 PM#
                  SYSTEM - 01/04/2015 02:05:21 PM#
                  SYSTEM - 01/04/2015 02:09:13 PM#
                  SYSTEM - 01/04/2015 02:09:38 PM#
                  SYSTEM - 01/04/2015 02:13:22 PM#
              ];
              
              • Re: Hours
                Mohammad Khatimiti

                Time(TimeStamp#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 10, 22), 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm') as HrMin

                Hope this will help u...

                 

                Regards,

                Mohammad

                • Re: Hours
                  jagan mohan rao appala

                  Hi,

                   

                  Try this sample script

                   

                  Data:

                  LOAD

                  DatTime,

                  Timestamp(Timestamp#(Replace(DatTime,'SYSTEM - ', ''),'MM/DD/YYYY hh:mm:ss TT#'),'hh:mm') as Time;

                  Temp:

                  LOAD * INLINE [

                      DatTime

                      SYSTEM - 01/04/2015 01:28:51 PM#

                      SYSTEM - 01/04/2015 01:49:07 PM#

                      SYSTEM - 01/04/2015 02:05:21 PM#

                      SYSTEM - 01/04/2015 02:09:13 PM#

                      SYSTEM - 01/04/2015 02:09:38 PM#

                      SYSTEM - 01/04/2015 02:13:22 PM#

                  ];

                   

                  Regards,

                  Jagan.

                  • Re: Hours
                    Massimo Grossi


                    if you only want the time part use frac (bold)

                     

                    LOAD

                    TimeStamp,

                    timestamp(Timestamp#(subfield(TimeStamp,' - ',2),'DD/MM/YYYY hh:mm:ss TT#'), 'hh:mm') as DateTime_hhmm,           // date and time hh:mm

                    time(frac(Timestamp#(subfield(TimeStamp,' - ',2),'DD/MM/YYYY hh:mm:ss TT#')), 'hh:mm') as Time_hhmm;    // time hh:mm

                    LOAD * INLINE [

                        TimeStamp

                        SYSTEM - 01/04/2015 01:28:51 PM#

                        SYSTEM - 01/04/2015 01:49:07 PM#

                        SYSTEM - 01/04/2015 02:05:21 PM#

                        SYSTEM - 01/04/2015 02:09:13 PM#

                        SYSTEM - 01/04/2015 02:09:38 PM#

                        SYSTEM - 01/04/2015 02:13:22 PM#

                    ];

                    • Re: Hours
                      reshma paturi

                      Hi,

                      Please find the attached QVW file