Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mhmmd_srf
Contributor II

Date format is not working in Where Clause

Hi All,

I have date format with millisecond. I am converting the time into DD-MON-YY and trying to apply this in where clause. But it is not working. Below is my script:

Transaction:

LOAD "CORE_ID",

     TAG3,

    TAG4,

    DIRECTION;

SQL SELECT "CORE_ID",

     TAG3,

    TAG4,

    DIRECTION

//FROM DCCE."SMR_CC_PROCESS" where TAG4 >='22-Sep-16 01.00.45.000000327 AM' ;

FROM DCCE."SMR_CC_PROCESS" where Date(Date#((Left(TAG4,18)),'DD-MMM-YY hh.mm.ss'),'DD-MMM-YY hh.mm.ss') >='22-Sep-16 01.00.45' ;

First clause (commented out) is working fine, however second is not working properly.

Could you help me on this.

Regards,

Sarif

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Date format is not working in Where Clause

As Stefan said, the problem is that you cannot use Qlik functions in SQL statement.


But, in your particular example, you can try

FROM DCCE."SMR_CC_PROCESS" where TAG4 >='22-Sep-16 01.00.45' ;

I think it will return the result you want(?)

12 Replies
MVP
MVP

Re: Date format is not working in Where Clause

Everything after SQL SELECT is sent to the  SQL driver and executed by the driver. You can't use QV functions like Date#() etc. in this part. Use SQL functions only.

mhmmd_srf
Contributor II

Re: Date format is not working in Where Clause

okk...Stefan.. let me try...

millan123
Contributor II

Re: Date format is not working in Where Clause

Hi Sarif,

You got the Solution?? yes then mark answer as correct/Helpful

mhmmd_srf
Contributor II

Re: Date format is not working in Where Clause

definitely....first let me try ...once it is resolved...I will surely mark as usefull..

mov
Esteemed Contributor III

Re: Date format is not working in Where Clause

As Stefan said, the problem is that you cannot use Qlik functions in SQL statement.


But, in your particular example, you can try

FROM DCCE."SMR_CC_PROCESS" where TAG4 >='22-Sep-16 01.00.45' ;

I think it will return the result you want(?)

MVP
MVP

Re: Date format is not working in Where Clause

Just to add / clarify my above statement:

You can add one QV specific piece to the SQL SELECT ...; part, this is a dollar sign expanded variable, like

SQL SELECT

...

FROM TABLE

WHERE  FIELD = '$(vVariable)';

The variable is expanded to text before the complete statement is sent to the driver.

mhmmd_srf
Contributor II

Re: Date format is not working in Where Clause

That we tried..but did not worked. That's why I thought to check with hardcoding.

MVP & Luminary
MVP & Luminary

Re: Date format is not working in Where Clause

I would rather say to use the right to_date() function on DB level to do a proper numerical comparison in Where Clause. But don't know what vendor (Oracle?) this is..

mov
Esteemed Contributor III

Re: Date format is not working in Where Clause

Yes, in general it would be right to use database own date/time functions, depending on what DB it is.  In this particular case formatting doesn't look necessary.