topic Re: Date/Time field in QlikView

Date/Time field

Anonymous — Thu, 11 Sep 2014 12:54:52 GMT

Hi guys,

Would anyone know how to strip out the time from a date/time field in a SQL table.

It is displayed like this in the table

2014-04-13 05:50:10.013

I would like to be able to analysis the data by time eg 05:50 from the example above.

Not sure if this is possible or not?

Thanks a lot

Re: Date/Time field

MK_QSL — Thu, 11 Sep 2014 12:59:26 GMT

Considering your above fieldname from SQL table is TIME, you can achieve the same by preceding load in QlikView Script...

Load

Time(Frac(Timestamp#(TIME,'YYYY-MM-DD hh:mm:ss.fff')),'hh:mm') as RequiredField;

TableName:

SQL SELECT

TIME,

SecondField,

ThirdField......

.....

....

From .....

Re: Date/Time field

ecolomer — Thu, 11 Sep 2014 12:59:47 GMT

You can try:

Time(Date, 'hh:mm')

Re: Date/Time field

Anonymous — Thu, 11 Sep 2014 13:02:14 GMT

time(date#('2014-04-13 05:50:10.013','YYYY-MM-DD hh:ss:sss'),'hh:mm')

Re: Date/Time field

ychaitanya — Thu, 11 Sep 2014 13:02:44 GMT

You can try this as well

Mid(Timefield,12,5)

Re: Date/Time field

anbu1984 — Thu, 11 Sep 2014 13:10:45 GMT

time(timestamp#('2014-04-13 05:50:10.013','YYYY-MM-DD hh:mm:ss.fff'),'hh:mm')

Re: Date/Time field

Thu, 11 Sep 2014 13:34:38 GMT

Try using the function extract(time from fieldname) under the sql statement.

Hope this helps

Re: Date/Time field

Anonymous — Tue, 16 Sep 2014 06:46:13 GMT

Thanks a lot for your help guys. I used the SQL statement to get this information and this works great..

Re: Date/Time field

anbu1984 — Tue, 16 Sep 2014 06:54:00 GMT

You can use extract to get year, month, day,hour, minute, second from timestamp in sql

Re: Date/Time field

Anonymous — Tue, 16 Sep 2014 06:58:27 GMT

Thank you I will bear that in mind.

What i would like to do is a list box to basically choose the time of orders so between 10:00-11:00, 11:00-12:00, 12:00-13:00 etc.

Would this be achievable do you think?

Re: Date/Time field

anbu1984 — Tue, 16 Sep 2014 07:05:41 GMT

Yes. Create time range column based on your timestamp field in sql like this

select extract(hour from timestamp_fld) || '-' || (extract(hour from timestamp_fld)+1) from table;