Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
time(time#('124215', 'hhmmss'))
and
time(time#('91234', 'hmmss'))
both work
Try
TIME(TIME#(YourField,'hhmmss'),'hh:mm:ss') AS Time
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
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
Thanks Jonathan - all becomes clear !
Thanks to Alan & Colin for their suggestions
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)