I have time field which have values such as 9AM-10PM, 10:00AM-11:00PM, I want to change 9AM-10PM to 9:00AM-10:00 PM, to normalize field in sameformat.I tired strftime(strptime(time_field,"%H%p-%H%p"),"%H:%M%p-%H%:%M%p") But its not working, I also tried convert() and fieldformat but no luck. Any idea how can I achieve this ?
hi @shugup2923,You can use replace function.
| eval time=if(like(time, "%:00%"), time, replace(replace(time, "AM", ":00AM"), "PM", ":00PM"))
View solution in original post
| eval timing=case(Match(timing, "\d*:\d*\w*-\d*:\d*\w*"), timing,Match(timing, "\d*:\d*\w*-\d*\w*"), replace(timing,"PM",":00PM"),Match(timing, "\d*\w*-\d*:\d*\w*"), replace(timing,"AM",":00AM"),true(), replace(replace(timing, "AM", ":00AM"), "PM", ":00PM"))
Perfect this worked, just I did small modification - used match instead of like as there are some other values as well. Approach is very useful