extracting time

[gz]

Hi, I have column data_start with date in format 2017-03-08 07:12:51.000000
I need extract only time in hh:mm:ss
I tried 

Mid(data_start, 12, 8) as data_start_time
Time(Time#(data_start, 'hh:mm:ss')) as data_start_time

Time(Timestamp#(data_start, 'YYYY-MM-DD hh:mm:ss.ffffff')) as data_start_time

Time(Mid(data_start, 12, 8)) as data_start_time

nothing works

[RafaelBarrios · Accepted solution]

Hi @gz 

as Qlik will understand data as a timestamp without Timestamp#()

you can do 

=time(start_Date)

if for any reason you need to split in script, you can

Qlik should understand those as date and time fields

time() to change output format

Hope this helps.

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!

best,

[gz]

this dosen't work 

Time(SubField(data_start,' ',2),'hh:mm:ss') as data_start_final

[QFabian]

Hi @gz  please try this option.

Script: 

SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD hh:mm:ss';

Aux:
Load * INLINE [
DateField
2017-03-08 07:12:51.000000
];

Load
DateField as Date_Ori,
Timestamp(DateField) as Date
Resident Aux;

exit script;

[gz]

In Script i'm using 
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

[RafaelBarrios]

strange 

but i see you have it on date_start_time3

are you doing any calculation ?

can you paste all section script ?

Best,

[marksouzacosta · Accepted solution]

Hi @gz,

Please try the code below - the highlighted bold part is the only one you need.

MyTable:
Load
DateTimeField,
Num(DateTimeField) AS DateTimeFieldNumeric,
Floor(DateTimeField) AS DateNum,
Frac(DateTimeField) AS TimeNum,
Date(Floor(DateTimeField)) AS Date,
Time(Frac(DateTimeField)) AS Time
INLINE [
DateTimeField
2017-03-08 07:12:51.000000
];

Qlik stores Date and Time values as integers + decimals. The integer part is the Date while the decimal is the time. Floor extracts the integer part, meaning only the Date, without time. The Frac is the opposite. 

Date and Time functions are only FORMAT functions, they do not change the VALUE of the field - even if they look changed in your charts. This is very important to keep in mind. 

Regards,

Mark Costa

[gz]

[bq_factoring_invoice_status_history]:

LOAD id_faktury as id_faktury_his , 

id_sprawy, 

data_start, 

data_stop, 

status_faktury,

    Date(Left(data_start, 10)) as data_start_date, //  YYYY-MM-DD

    Time(Timestamp#(data_start, 'YYYY-MM-DD hh:mm:ss.ffffff')) as data_start_time,

    Time(Time#(data_start, 'hh:mm:ss')) as data_start_time1,

    Time(Mid(data_start, 12, 8),'hh:mm:ss') as data_start_time2,

    Time(data_start) as data_start_time3,

    Time(SubField(data_start,' ',2),'hh:mm:ss') as data_start_final

    ;

[bq_factoring_invoice_status_history]:

SELECT "id_faktury",

"id_sprawy",

"data_start",

"data_stop",

"status_faktury"

FROM "qlick"."bq_factoring_invoice_status_history";