Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
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
ashfaq_haseeb
Champion III
Champion III

Hi look at the below code.

All of them working.

t1:

LOAD Time,

time(Time) as Time1,

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

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

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

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

FROM

QVTime.xlsx

(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

its_anandrjs

Use this line to load the field as time

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

Let me know if not works.

nam
Former Employee
Former Employee

Hi if its a formatting problem then just use the subfield function that will get you the correct answer:

maketime(subfield(yourdatefield,':',4),subfield(yourdatefield,':',5),subfield(yourdatefield,':',6)) as Time

you can do that in the script and will work a treat

its_anandrjs

Try to load only this way

Load

Time,

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

Time(Left(Time(Time,'hh:mm:ss TT'),8),'hh:mm:ss')  as NewTime2 // Format you get hh:mm:ss

FROM

[QVTime.xlsx]

(ooxml, embedded labels, table is Sheet1);

Not applicable
Author

Arrrg this is driving me nuts... it doesn't work?

Ive noticed that in excel when the file is opened only 00/01/1900 appears but when you double click the cell then the full date and time appears

ie 00/01/1900 15:29:22

tresesco
MVP
MVP

That is because of formatting. Actual underlaying data is having timstamp with time. See my attached qvw in above post.

Not applicable
Author

Thanks so much for all the replies

For some reason my QlikView is showing personal edition and not licenced and so I cant open the QVW, Ive faulted the licence issue with our IT but its gonna take a little while to resolve, is there any thing you can post in here in the meantime.. ?

MarcoWedel

Hi Glenn,

using the time() function should be enough:

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

FROM [http://community.qlik.com/servlet/JiveServlet/download/550208-111495/QVTime.xlsx]

(ooxml, embedded labels, table is Sheet1);

QlikCommunity_Thread_122216_Pic1.JPG.jpg

If you can ensure that the date part of your excel data will always be 00/01/1900 then there is no need for any removing the integer part of your excel timestamp (as e.g. the proposed frac() function would do). The integer part of the numeric representation of your loaded timestamps would be 0 already.

hope this helps

regards

Marco

its_anandrjs

If you need time then change format in the excel as Time and save the excel file.

Not applicable
Author

it doesn't work?

Ive noticed that in excel when the file is opened only 00/01/1900 appears but when you double click the cell then the full date and time appears

ie 00/01/1900 15:29:22

For some reason my QlikView is showing personal edition and not licenced and so I cant open the QVW, Ive faulted the licence issue with our IT but its gonna take a little while to resolve, is there any thing you can post in here in the meantime.. ?