AnsweredAssumed Answered

identify date separator and change it

Question asked by glenmac on Aug 1, 2017
Latest reply on Aug 3, 2017 by 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

Outcomes