topic Re: Reformatting database time field in QlikView

Reformatting database time field

Thu, 13 Mar 2014 12:52:40 GMT

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

Re: Reformatting database time field

Colin-Albert — Thu, 13 Mar 2014 12:57:46 GMT

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

and

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

both work

Re: Reformatting database time field

rustyfishbones — Thu, 13 Mar 2014 12:58:23 GMT

Try

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

Re: Reformatting database time field

Thu, 13 Mar 2014 13:07:13 GMT

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

Re: Reformatting database time field

jonathandienst — Thu, 13 Mar 2014 13:21:59 GMT

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

Re: Reformatting database time field

Thu, 13 Mar 2014 13:25:49 GMT

Thanks Jonathan - all becomes clear !

Thanks to Alan & Colin for their suggestions

Re: Reformatting database time field

Colin-Albert — Thu, 13 Mar 2014 13:28:01 GMT

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)