Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

vladislav96
New Contributor II

How to convert datetime to time

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.
InvoiceDate.JPG

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 .

17 Replies
vishsaggi
Esteemed Contributor III

Re: How to convert datetime to time

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

May be this

LOAD Time(Frac(Date)) as Time,

TimeStamp(Date) as DateTime,

Date(Floor(Date)) as Date;

SELECT

INVOICEDATE as Date

FROM VENDINVOICEJOUR

thiago_pinho
New Contributor III

Re: How to convert datetime to time

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

Right?

vladislav96
New Contributor II

Re: How to convert datetime to time

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

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

Highlighted
vladislav96
New Contributor II

Re: How to convert datetime to time

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

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

vladislav96
New Contributor II

Re: How to convert datetime to time

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

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

Capture.PNG