Date format is not working in Where Clause

[mhmmd_srf]

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

[swuehl]

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]

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

[millan123]

Hi Sarif,

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

[mhmmd_srf]

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

[Anonymous · Accepted solution]

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(?)

[swuehl]

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]

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

[rbecher]

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

[Anonymous]

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.