5 Replies Latest reply: May 31, 2011 3:57 AM by Gerhard Laubscher RSS

    Date format problem

    Gerhard Laubscher

      Hi,

       

      I need help with a script that is already doing my head in. I use application extract with the following two fields:

      [Log Date]

      [Decision Date]

       

      Both are in the following format (in a delimited text file):

      27-MAY-2011 14:46:28 (i.e. DD-MMM-YYYY hh:mm:ss)

       

      Using these 2 fields I determine the process time of an application. At the moment I determine this in DAYS, but I need to do so in hours. Please can someone help me to amend the below script so it takes the hh:mm:ss into consideration and so my [Process Time] field shows in hours:

       

      Date(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Log Date],

            Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY')) as [Decision Date],

           weekday(Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY'))) as [Decision Day],

           month(Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY'))) as [Decision Month],

           year(Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY'))) as [Decision Year],

           if([Decicion Result]='A','Approved','Declined') as [Decision Result],

           if(Date#( left( [Log Date],11), 'DD-MMM-YYYY') =date#( left([Decision Date],11),'DD-MMM-YYYY'), 'Same Day', date#( left([Decision Date],11),'DD-MMM-YYYY')-Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Process Time],

       

      Thanks in advance,

       

      Gerhard

        • Date format problem

          Hello Gerhardl

          try this:

            if(Date#([Log Date], 'DD-MMM-YYYY hh:mm:ss') =date#([Decision Date],'DD-MMM-YYYY hh:mm:ss'), 'Same Time', date#([Decision Date],'DD-MMM-YYYY hh:mm:ss')-Date#([Log Date], 'DD-MMM-YYYY hh:mm:ss') as [Process Time],

          • Date format problem
            Erich Shiino

            Try this:

            ( Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -
             

             

            Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 as [Process Time]  // in Hours

             

             

             

            The timestamp will evaluate your string as date+time

            The difference between two timestamps will be a difference in days including fractions for hours.

            So, if you get 12.5, this means 12 days and half day or 12 days and 12 hours.

             

            I multiplied by 24 to get the result in hours. So, for 12.5 you should get 300 hours.

             

            Hope this helps,

             

            Erich

              • Re: Date format problem
                Gerhard Laubscher

                I can’t seem to reply to threads – javascript: void(0);

                 

                Hope this works. Thank you Erich, that works. Is there any way to limit the field to 1 or 2 decimals – it currently shows about 15. So one hour and a few seconds will show as 1.0000000000000164.

                 

                Also, I would like to change the process time to show either less than an hour, or 1 – 2 hours, 2 – 3 hours, or more than 3 hours.

                 

                Thanks for the help. This is my script currently:

                 

                Timestamp(Timestamp#( , 'DD-MMM-YYYY hh:mm:ss')) as ,

                     Timestamp(Timestamp#( , 'DD-MMM-YYYY hh:mm:ss')) as ,

                     Timestamp( Timestamp#(, 'DD-MMM-YYYY hh:mm:ss') - Timestamp(Timestamp#( , 'DD-MMM-YYYY hh:mm:ss') )) * 24 as ,

                 

                Gerhard Laubscher

                Tenacity (a Pepkor company)

                P.O. Box 6387, Parow East, 7501, South Africa

                 

                T: +27 (0)21 928 1017

                F: +27 (0)21 928 1012

                C: +27 (0)82 080 4190

                 

                www.tenacityinc.co.za<http://www.tenacityinc.co.za/>

                gerhardl@tenacityinc.co.za<mailto:stephnik@tenacityinc.co.za

                  • Re: Date format problem
                    Erich Shiino

                    You can adjust the presentation of the field in Settings -> Document Properties -> Number (it's similar to excel)

                    You could also force it on script level ( you may need to try some different settings) :

                     

                    num ( ( Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -
                     

                     

                    Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24, '##0.00') as [Process Time]

                     

                    For the ranges, you could use intervalmatch, but since it's simple, you could just write an if statement:

                    if(

                    (Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -

                     

                    Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 <1, 'Less than 1-hour',

                    if(

                    (Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -

                     

                    Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 <2, '1-2 hours',

                    if(

                    (Timestamp#([Decision Date], 'DD-MMM-YYYY hh:mm:ss') -

                     

                    Timestamp#( [Log Date], 'DD-MMM-YYYY hh:mm:ss') ) * 24 <3, '2-3 hours','3+ Hours'

                     

                    )

                    )

                    )

                     

                    Hope I didn't forget any ( or )