Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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(?)
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.
okk...Stefan.. let me try...
Hi Sarif,
You got the Solution?? yes then mark answer as correct/Helpful
definitely....first let me try ...once it is resolved...I will surely mark as usefull..
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(?)
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.
That we tried..but did not worked. That's why I thought to check with hardcoding.
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..
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.