Date format problem

HemaV · Post by **HemaV** » Sat Jul 18, 2009 6:44 am

I have the date value in the oracle source database table for the column(varchar datatype) I have value either as "0000/00/00 00:00:00" or "00/00/00 00:00:00".
When I select these records and try to insert into a target database table when I try to insert into the column(date datatype), I am getting error like invalide month.

My job looks like:
SourceOracleStage----->TransformerStage----->TargetOracelStage.

Can anyone of you suggest me how to insert a NULL value for the above two formats. Is there any generalized method where I can check if i have 0 date values then insert a NULL value?

chulett · Post by **chulett** » Sat Jul 18, 2009 7:24 am

I would try stripping all of the non-numeric values and then check for zero:

Code: Select all

If DIGITS(Link.DateField) = 0 Then @NULL Else Link.DateField

(assuming no other transformations are needed)

ray.wurlod · Post by **ray.wurlod** » Mon Jul 20, 2009 4:34 pm

The first date is YMD order. Is the second date also YMD order? If not, what is the order of the day, month and year components, and have you informed Oracle that such is the case?