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

[rbecher]

Why? Are you sure to meet the default format as string?

[Anonymous]

Here is what I see from the OP.  The condition is

Time >= "Cutoff Time"

And, he takes effort to convert "Time+delta" to "Time" by trying to remove fraction of second (delta).

But, if

Time >= "Cutoff Time" is true,  then

Time+delta >= "Cutoff Time" is true as well (unless delta is negative - not the case with time), so the effort to eliminate delta in this place is not needed.

(I'd probably remove it in the load part from the TAG4, but it's a different story.)

Do you think I'm missing something?

[rbecher]

Don't know how Time is converted to a String here, therefore String comparison could be wrong..