cast syntax

[dpit]

I have tried this syntax in business objects (a reporting tool) with Teradata syntax since we are trying to access data.

The date field is stored as 20030502.

I have written this
Cast(substr(IVR.Local_Order_Entry_IVR.DATES,1,4) || '-' || substr(IVR.Local_Order_Entry_IVR.DATES,5,2) || '-' || substr(IVR.Local_Order_Entry_IVR.DATES,7,2) As Date)

But it comes back with a syntax error: Invalid date supplied for DATES. Any suggestions?

 

[BillDHS]

Try adding a format statement after the DATE (format 'yyyy-mm-dd')

 

[dnoeth]

The CAST is ok, so there's probably an invalid date in column DATES.
That's why you should store a date in a DATE column ;-)

You could try following select to find those values:
select dates
from tab
where dates not in
(select calendar_date (format 'yyyymmdd') (char(8))
from sys_calendar.calendar)

Btw, why don't you use a simple
DATES (DATE, FORMAT 'YYYYMMDD')
instead of the SUBSTRINGs?

Dieter