CODEWITHSUNDEEP
teradata
Koushik Chandra
Koushik Chandra

Reputation: 1491

Teradata BTEQ - Invalid timestamp issue

I am trying to execute an sql through TERADATA BTEQ and getting the below error.

But the same sql is running good when getting executed in Teradata SQL assistant.

WHERE S_ORDER_ITEM.LAST_UPD  BETWEEN CAST( (('20050614' (DATE, FORMAT 'YYYY-MM-DD')) -1 (CHAR(10)) )|| ' ' || '22:00:01' AS TIMESTAMP(0))  AND CAST( (('20050614' (DATE, FORMAT 'YYYY-MM-DD')) (CHAR(10)) )|| ' ' || '22:00:00' AS TIMESTAMP(0) )
 *** Failure 2666 Invalid date supplied for S_ORDER_ITEM.LAST_UPD.

Upvotes: 0

Views: 11458

Answers (2)

Abhikash Babu
Abhikash Babu

Reputation: 1

timestemp error occurs when your phone time is wrong The solution is to correct your device time

Upvotes: -2

dnoeth
dnoeth

Reputation: 60482

The error message indicates that S_ORDER_ITEM.LAST_UPD is not a date/timestamp (char?) and the automatic typecast to a timestamp fails due to bad data.

This part '20050614' (DATE, FORMAT 'YYYY-MM-DD') should never work, because the string doesn't match the Format.

There's no need for casting a string to a date, because the recommended way to write a date literal is simpler and shorter, using Standard SQL DATE '2005-06-14'.

If the date is actually known you better write 

BETWEEN TIMESTAMP '2005-06-13 22:00:01'
    AND TIMESTAMP '2005-06-14 22:00:00'

Otherwise don't cast to/from string, use datetime calculations instead:

BETWEEN Cast(DATE '2005-06-14' AS TIMESTAMP(0)) - INTERVAL '01:59:59' HOUR TO SECOND
    AND Cast(DATE '2005-06-14' AS TIMESTAMP(0)) + INTERVAL '22:00:00' HOUR TO SECOND

Edit:

If you can't change the input format you need to apply another FORMAT:

BETWEEN Cast((('20050614' (DATE, Format 'YYYYMMDD')) -1 (Format 'yyyy-mm-dd')) || ' ' || '22:00:01' AS TIMESTAMP(0))
    AND Cast((('20050614' (DATE, Format 'YYYYMMDD'))    (Format 'yyyy-mm-dd')) || ' ' || '22:00:00' AS TIMESTAMP(0))

Upvotes: 0

Related Questions