Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MVP
MVP

Use Frac(). Like:

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

FROM <>;

its_anandrjs

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
Champion III
Champion III

If it is date field

then use

=Time(DATE_TIME_Field)

Regards

ASHFAQ

thomas_skariah
Creator III
Creator III

Hi,

PFA, you can get help from this.

Regards,

Tom

Not applicable
Author

Doesn't work it just returns " -  "

its_anandrjs

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
Author

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
MVP
MVP

It worked with me. PFA

nlmmaurya
Contributor II
Contributor II

Hi,

Try this if your Date format would be always same.

LOAD Time,

     Right(Time,11) as NewTime       

FROM<>