Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date and Time to Time Only

Help nothing works. I want to convert a date and time field to time only

The field I'm converting always looks like this  with dates as 00/01/1900 with the time after ie

  00/01/1900 00:01:03

  00/01/1900 10:05:13

  00/01/1900 15:29:22

  00/01/1900 20:44:52

nothing seems to extract the time only any ideas

22 Replies
tresesco
Not applicable

Re: Date and Time to Time Only

Use Frac(). Like:

LOAD Time(Frac(Time),'hh:mm:ss') as Time

FROM <>;

its_anandrjs
Not applicable

Re: Date and Time to Time Only

Load like and try one of this

Load

Time,

Timestamp#(Time,'M/D/YYYY h:mm:ss TT') as NewTime

Time(Time,'hh:mm:ss') as NewTime2,

Time(Time,'hh:mm:ss TT') as NewTime3

From location;

And it seems in excel file your Time field is not formatted as the proper Time or Date field try one of this.

ashfaq_haseeb
Not applicable

Re: Date and Time to Time Only

If it is date field

then use

=Time(DATE_TIME_Field)

Regards

ASHFAQ

thomas_skariah
Not applicable

Re: Date and Time to Time Only

Hi,

PFA, you can get help from this.

Regards,

Tom

Not applicable

Re: Date and Time to Time Only

Doesn't work it just returns " -  "

its_anandrjs
Not applicable

Re: Date and Time to Time Only

Try one of this

Time(Time,'hh:mm:ss TT') as NewTime1,

Time(Time,'hh:mm:ss') as NewTime2,

Time(Time,'hh:mm:ss TT') as NewTime3

Not applicable

Re: Date and Time to Time Only

That the problem the formatting of the excel file seems strange but all the files come like this and I need an extraction which will put it right

tresesco
Not applicable

Re: Re: Date and Time to Time Only

It worked with me. PFA

nlmmaurya
Not applicable

Re: Date and Time to Time Only

Hi,

Try this if your Date format would be always same.

LOAD Time,

     Right(Time,11) as NewTime       

FROM<>