how do i make this format 1230 , 20 , 1 into intervals or time in this format 12:30 , 00:20 , 00:01

[wizardo]

hi

i have a field that comes from my database source as a string containing values that represent intervals or time of day

for example 1234 means 12:34

how do i turn them into the correct form with out too much use of IF's and string manipulation?

i can easyly enough take the string when its full and do this

left(string,2) & ':' & right(string,2)

but that only works when the string is 4 digits. if its less i need to start checking its length and add zeroes and stuff and it gets ugly fast

anyone maybe have an idea?

some form of regex maybe?

i have many fields like that in a huge database and it is really very time consuming to use it like that . hope you can help

Mansyno

[#2 · Accepted solution]

Hi

Try this in the script :

time(time#(num( [yourtimefield] , '0000'), 'hhmm')) as yourNewTime

JJ

[wizardo]

hi

thanks:)

it helped a lot

since i needed the format a bit different this is what i ended up with

interval(time#(num( [yourtimefield] , '0000'), 'hhmm'),'hh:mm') as yourNewTime;