Facing issue with timestamp’s milli seconds.
*From below query, 2nd record should be picked but result is last record.
*Not considering the milli seconds difference.
sel * from DT_FSLDM_LD_V.LD_PRTY_RLTD_36016 L WHERE prty_id = 39740899
AND prty_rltd_strt_dttm >CAST('2016-08-05 19:19:23.260193' AS TIMESTAMP(6))
prty_rltd_strt_dttm is defined in table as
<prty_rltd_strt_dttm timestamp(6) TITLE 'Party Related Start Dttm' NOT NULL>
Below 3 records – Date is same but difference in milli seconds.
What version of Teradata are you using? It works for me in 16.20. I think older releases might have ignored microseconds, even though they were stored accurately. (By the way, the last 3 digits, millionths of a second, represent microseconds; milliseconds are thousandths of a second.)
Is this a query run in SQL Assistant, .NET or ODBC?
Try a timestamp literal instead of a cast:
TIMESTAMP '2016-08-05 19:19:23.260193'
Can you check Explain for the exact value?
You could also try a character comparison instead of timestamp comparison, such as
cast(prty_rltd_strt_dttm as char(26)) > '2016-08-05 19:19:23.260193'
(but that would use a little more CPU time).