String to Date conversion issue

dxk9 · Post by **dxk9** » Mon Jun 11, 2012 1:01 am

Hi,

I have a input sequential file with a field value '2010-05-16'. Need to convert this into Date & load it into a Teradata table.

Based on Dsxchange suggestions, I tried different combinations of oconv & iconv. But it doesn't work.

The error says:

Value treated as NULL
Attempt to convert String value "" to Date type unsuccessful

Please help !!

Thanks,
dxk9

zulfi123786 · Post by **zulfi123786** » Mon Jun 11, 2012 2:06 am

you need to use as shown

Code: Select all

 Iconv(input.Date,"D-YMD")

ray.wurlod · Post by **ray.wurlod** » Mon Jun 11, 2012 2:11 am

What is your default date format for Teradata? (You may need to ask your DBA.)

dxk9 · Post by **dxk9** » Mon Jun 11, 2012 2:25 am

Zulfi,

Thanks !! Your suggestion worked

Ray,

As of now its working, will also know the Teradata default date format from DBAs for future reference.

Thanks,
dxk9

chulett · Post by **chulett** » Mon Jun 11, 2012 6:27 am

So Teradata needs the date in internal format? Don't recall hearing that before. Have you verified that you get the correct date in the database rather than it works because the job no longer throws the error?

ray.wurlod · Post by **ray.wurlod** » Mon Jun 11, 2012 8:37 pm

I would expect a server job to behave this way. One exception is ODBC, where you have to specify this behaviour by specifying Date as the data element.

chulett · Post by **chulett** » Mon Jun 11, 2012 9:13 pm

Sorry for being especially dense tonight but why would you 'expect a Server job to behave this way'? I must be missing something fundamental here...

ray.wurlod · Post by **ray.wurlod** » Mon Jun 11, 2012 9:18 pm

Because, in server jobs, the representation of the Date data type is "internal format". Most Connectors (and their predecessors) perform the conversion automatically where the metadata are consistent.

chulett · Post by **chulett** » Mon Jun 11, 2012 9:52 pm

Ah... having dealt basically 100% with Oracle where I would always treat a DATE as a full Timestamp with explicit control over the time portion, that never really clicked for me.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 12, 2012 12:21 am

Oracle is ...
_________________ ... different