topic Re: How to convert datetime to time in QlikView

How to convert datetime to time

Anonymous — Wed, 08 Nov 2017 13:21:16 GMT

Hi everyone,
I am making a Filter with Calendar Object from Date to Date.I use database(SQL).I don't have problem to insert field from database to Qlikview. I make in Qlikview one field with INVOICEDATE(Time)and two calendars object . When I choose from date like : 01.10.2016 to 01.11.2016 and Calendar doesn't work. I mean , that this is happening , because when i get field form database - field datatype is datetime. I tried with other field , which is only date and Calendar work correctly.My Question is how to convert or cast field in Script to datatype date.

SELECT

INVOICEDATE(floor(Date)) as Date

FROM VENDINVOICEJOUR

Below is my qvw file . If someone can fix it and give it back to me.
Thank you .

Re: How to convert datetime to time

vishsaggi — Wed, 08 Nov 2017 16:33:37 GMT

May be try this?

LOAD Date(Date#(Date, 'YYYY-MM-DD hh:mm:ss'), 'DD.MM.YYYY') AS InvoiceDate;

SELECT

INVOICEDATE as Date

FROM VENDINVOICEJOUR;

Re: How to convert datetime to time

sunny_talwar — Wed, 08 Nov 2017 16:35:28 GMT

May be this

LOAD Time(Frac(Date)) as Time,

TimeStamp(Date) as DateTime,

Date(Floor(Date)) as Date;

SELECT

INVOICEDATE as Date

FROM VENDINVOICEJOUR

Re: How to convert datetime to time

Anonymous — Wed, 08 Nov 2017 16:38:52 GMT

You may Use #Date(DataField,'DD.MM.YYYY')

Right?

Re: How to convert datetime to time

Anonymous — Thu, 09 Nov 2017 08:24:09 GMT

stalwar1‌ I am not sure , whether the code for script is right , because the calendar objects don't work. Can you review my prova.qvw file and tell me why ?

Thank you.

Re: How to convert datetime to time

sunny_talwar — Thu, 09 Nov 2017 16:17:51 GMT

Can you rerun your file and attach is back

LOAD Time(Frac(Date)) as Time,

TimeStamp(Date) as DateTime,

Date(Floor(Date)) as Date;

SELECT

INVOICEDATE as Date

FROM VENDINVOICEJOUR

Re: How to convert datetime to time

Anonymous — Fri, 10 Nov 2017 09:17:11 GMT

The Code for script work correctly , but calendar objects not . Can you see my qlikview file and tell my why ?

Re: How to convert datetime to time

sunny_talwar — Fri, 10 Nov 2017 12:11:23 GMT

What is wrong with the calendar object? I am not sure what you expect it to do and what is it doing?

Re: How to convert datetime to time

Anonymous — Fri, 10 Nov 2017 13:12:21 GMT

For example :when i first choose from "FROM CALENDAR" - 01/01/2017 select all date in field Datem (i think it is right to do that) and than when I select "TO CALENDAR" - 01/03/2017 doesn't select anything in Datem field.

I want to set a date from ..... to.... . work only if it chose by default(last choose before exit the qlik) from to calendar.

If I make it with inpux box work correctly ?

Re: How to convert datetime to time

sunny_talwar — Fri, 10 Nov 2017 13:30:27 GMT

Change these two settings in the environmental variables of your script and rerun to see if it works now

Re: How to convert datetime to time

Anonymous — Fri, 10 Nov 2017 14:56:00 GMT

I don't think it works. I will try to explain it in clearly.

Start file g.qvw that you sent me or my (does't matter who).Click on Calendar object "FROM" and select (01/01/2017) and after this action are selected all lines with date 2017... for now it looks okei. If you look in Current Selections you will see that set value and from calendar object "TO" i think the problem is here !

My task is when I start Qlik , first select in Calendar object"FROM" - (01/01/2017) , second selecet in calendar object "TO" - (01/03/2017) and Field Datem looks like this :

And now Filed Datem select only 4 lines and work correctly but I make this from Input box...

Please look at it again . Why from inputbox work from calendars object not

Thank you

Re: How to convert datetime to time

sunny_talwar — Fri, 10 Nov 2017 15:02:19 GMT

Vladislav - I wanted you to reload the application with the new environmental variable settings that I sent you... you needed to test once you have reloaded... Anyways, I have attached a version of the application.... see if this works... (I had to do a binary load to fix the issue)

Re: How to convert datetime to time

Anonymous — Fri, 10 Nov 2017 15:18:07 GMT

Now it is working , what do you make ?

The default SET in scripts are problem ?

Thank you so much!!!

Re: How to convert datetime to time

sunny_talwar — Fri, 10 Nov 2017 15:22:59 GMT

yes, change the two set statements and reload

Re: How to convert datetime to time

Anonymous — Fri, 10 Nov 2017 15:40:40 GMT

Can you tell me how to execute SQL query because it load only one filed .

LOAD Date(Floor(Date)) as Datem;

SQL SELECT

VENDTABLE.ACCOUNTNUM,

DIRPARTYTABLE.NAME,

VENDINVOICEJOUR.INVOICEID,

VENDINVOICEJOUR.INVOICEDATE AS Date,

VENDINVOICEJOUR.INVOICEAMOUNT,

VENDINVOICEJOUR.CURRENCYCODE

FROM DIRPARTYTABLE

INNER JOIN VENDTABLE

ON DIRPARTYTABLE.RECID=VENDTABLE.PARTY

INNER JOIN VENDINVOICEJOUR

ON VENDINVOICEJOUR.INVOICEACCOUNT=VENDTABLE.ACCOUNTNUM

AND VENDINVOICEJOUR.DATAAREAID = VENDTABLE.DATAAREAID;

Re: How to convert datetime to time

sunny_talwar — Fri, 10 Nov 2017 15:42:25 GMT

Try this

LOAD *,

Date(Floor(Date)) as Datem;

SQL SELECT

VENDTABLE.ACCOUNTNUM,

DIRPARTYTABLE.NAME,

VENDINVOICEJOUR.INVOICEID,

VENDINVOICEJOUR.INVOICEDATE AS Date,

VENDINVOICEJOUR.INVOICEAMOUNT,

VENDINVOICEJOUR.CURRENCYCODE

FROM DIRPARTYTABLE

INNER JOIN VENDTABLE

ON DIRPARTYTABLE.RECID=VENDTABLE.PARTY

INNER JOIN VENDINVOICEJOUR

ON VENDINVOICEJOUR.INVOICEACCOUNT=VENDTABLE.ACCOUNTNUM

AND VENDINVOICEJOUR.DATAAREAID = VENDTABLE.DATAAREAID;

Re: How to convert datetime to time

Anonymous — Fri, 10 Nov 2017 15:44:44 GMT

Ahaaaaaaaaaaaaa Thank you so much friend

Re: How to convert datetime to time

sunny_talwar — Fri, 10 Nov 2017 15:46:49 GMT

No problem at all

Please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny