3 Replies Latest reply on Aug 3, 2017 9:32 AM by glenmac

    identify date separator and change it

    glenmac

      My database imports csv file from a commercial scheduling application every month, a few dozen records to track equipment usage.  The dates used to be in dd/mm/yyyy format and were stored in the Start Date field. My script would convert them to  mm/dd/yyyy and store in US_Date_Start field for display and calculations.

      The scheduling application now exports dates as mm-dd-yyyy.

       

      I would like to step through Start Date field in the new records and if  the separator is "/", run the old conversion, but if it is "-", convert to "/" and store the conversion in US_Date_Start.  that way if I import any old records, they will still process correctly.

       

      My attempts to change the "-" to "/" are failing:

      If [PatternCount(User Log::Start Date;"-") >=1]

        Set Field[Substitute(User Log::Start Date;"-";"/")]

      End If

       

      also tried

      SetFieldbyName[User Log::US_Date_Start;Substitute(User Log::StartDate;"-";"/")]

      What am I missing?

       

      Thanks,

      Glen

        • 1. Re: identify date separator and change it
          Mike Duncan

          You could create a calc to handle it as an actual date, then you have the flexibility to output in different formats. Here is a calc that should work, just replace "Field1" with your field:

           

          Let ( [

            $this.date  = Field1;

            $this.array = Substitute ( $this.date ; ["/" ; "¶"] ; ["-" ; "¶"] );

            $this.month = Case ( PatternCount ( $this.date ; "-" ) > 0 ; GetValue ( $this.array ; 1 ) ; GetValue ( $this.array ; 2 ) );

            $this.day   = Case ( PatternCount ( $this.date ; "-" ) > 0 ; GetValue ( $this.array ; 2 ) ; GetValue ( $this.array ; 1 ) );

            $this.year  = GetValue ( $this.array ; 3 ) 

           

          ];

           

          Date ( $this.month ; $this.day ; $this.year )

           

          )

          1 of 1 people found this helpful
          • 2. Re: identify date separator and change it
            glenmac

            thank you Mike, that worked! 

            Glen

            • 3. Re: identify date separator and change it
              glenmac

              Hi Mike,

              the Let function is new to me.  I'd like to modify your calc to use "/" or "-" to identify the source date format and load $this.month and $this.day correctly before running Substitute. 

              My modified calc is giving an error that the the number of "(" and ")" do not match.  But, they seem so to me, suggesting I'm missing something, possible in the last line.

               

              Let ( [

                $this.date  = User Log::Start Date;

              $this.month = Case ( PatternCount ( $this.date ; "-" ) > 0 ; GetValue ( $this.array ; 2 ) ; GetValue ( $this.array ; 1 ) );

              $this.day   = Case ( PatternCount ( $this.date ; "-" ) > 0 ; GetValue ( $this.array ; 1 ) ; GetValue ( $this.array ; 2 ) );

                $this.year  = GetValue ( $this.array ; 3 ) ;

               

              $this.format=( $this.month ; $this.day ; $this.year )];

                Date = Substitute ( $this.format; ["/" ; "¶"] ; ["-" ; "¶"] );

              )

               

              regards,

              Glen