Reformatting database time field

[#1]

Hi All,

I am aware you can fairly easily reformat a date field as follows :-

date(date#(OriginalDateLayout, 'YYYYMMDD'),'DD/MM/YYYY') AS NewDateLayout

but I am trying to achieve the same thing with time...

i.e. my time in the database table is for example 124215 (hhmmss) or 91234 (hmmss) which I just want to become

12:42:15 and 09:12:34

I can't find a time function that works the same way - does anyone have any ideas please ?

Thanks in advance

Bob

[Colin-Albert]

time(time#('124215', 'hhmmss'))

and

time(time#('91234', 'hmmss'))

both work

[rustyfishbones]

Try

TIME(TIME#(YourField,'hhmmss'),'hh:mm:ss') AS Time

[#4]

Hi Guys,

Following the two suggestions it only works partly i.e.

124215 becomes 12:42:15 - all ok, but

90117 becomes 18:11:07 ???

85500 becomes 13:50:00 ??

85719 becomes 14:11:09 ??

..the missing '0' at the start in the db seems to cause confusion 😞

??

Thanks

NIck

[jonathandienst · Accepted solution]

Hi

Because there are no internal delimiters in your format, Time# is misreading 90117 as 90 hours, 11 minutes, 7 seconds (which is 3 days (72 hours) + 18h 11min and 7 seconds. You could use this to correct the problem:

     =Time(Time#(Right('0' & OriginalDateLayout, 6), 'hhmmss'))

HTH

Jonathan

[#6]

Thanks Jonathan - all becomes clear !

Thanks to Alan & Colin for their suggestions

[Colin-Albert]

time(time#(right('000000' & YourTimeData , 6), 'hhmmss')

will give the time as hh:mm:ss whether the input data is 1-6 characters long

30 (00:0030)

1530 (00:15:30)

12345 (01:23:45)

123456 (12:34:56)