Archives of the TeradataForum

Message Posted: Sat, 05 Nov 2016 @ 14:23:03 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>