How to convert datetime to time

[Anonymous]

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 .

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

Now it is working , what do you make ?

The default SET in scripts are problem ?

Thank you so much!!!

[sunny_talwar]

yes, change the two set statements and reload

[Anonymous]

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;

[sunny_talwar]

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;

[Anonymous]

Ahaaaaaaaaaaaaa Thank you so much friend

[sunny_talwar]

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