Teradata timestamp’s Milli seconds

Database
Teradata Employee

Teradata timestamp’s Milli seconds

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.

Query :

sel * from DT_FSLDM_LD_V.LD_PRTY_RLTD_36016 L WHERE  prty_id = 39740899

--Filter

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.

Data.JPG

4 REPLIES 4
Highlighted
Teradata Employee

Re: Teradata timestamp’s 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.)

Teradata Employee

Re: Teradata timestamp’s Milli seconds

Current version is Teradata 15.10.

is there any way possible to get the result in 15.10

Tags (1)
Ambassador

Re: Teradata timestamp’s Milli seconds

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?

Teradata Employee

Re: Teradata timestamp’s Milli seconds

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