Invalid Julian day

[vinsashi]

Hi,
My source is Oracle 9i and target is Sql Server 2005.
My job is oracledatabasestage -> transformer -> odbcstage.
Previously job is running perfectly, now I got one new file is PMNEXTDUEDATE in that one I am getting nulls also. So in source I specified Nullable is yes and in target also I specified Nullable is yes
and I wrote derivation for that file like this:

Code: Select all

If IsNull(Into_Trns.PMNEXTDUEDATE) = 1 then '1900-01-01 00:00:00'  Else  If len(trim(Into_Trns.PMNEXTDUEDATE)) = 0 then '1900-01-01 00:00:00' Else Into_Trns.PMNEXTDUEDATE

My source contains 1,200,000 records if I excute job after 400,000 records the job is going to abort. I am gettting fatal error like INVALID Julian day" if I wrote derivation for that filed like this '1900-01-01 00:00:00' then all records loading

Thanks in Advance

[ray.wurlod]

Look at the incoming date/time at around record # 400,000. Report if there's anything different about it.

[vinsashi]

I Identified particular record where it aborting.
in that date coming like this 26-FEB-70.this value not inserting into SQL server table.Please give any idea

Thanks

[chulett]

How is that one different from any other record?

You should standardize your Oracle dates so they extract in that same ISO Timestamp format you mentioned in your derivation. Make the source column a Timestamp data type and (if the sql doesn't automatically generate this) use the to_char function to get the proper output:

Code: Select all

TO_CHAR(YourField,'YYYY-MM-DD HH24:MI:SS')

[asorrell]

By the way - IsNull is a Boolean function, which means it can be evaluated as True or False all by itself. You don't have to compare it to "1".

Replace

If IsNull(Into_Trns.PMNEXTDUEDATE) = 1

with

If IsNull(Into_Trns.PMNEXTDUEDATE)

And your code will look cleaner without changing execution.

Also - make sure you don't have any invisible control characters embedded in that field. If you are in UNIX you can usually do a cat -tv on that line (might be cat -Tv depending on the O/S).

[imfarhan]

Thanks Vinsashi
your code was quite helpful for me to handle Invalid Julian Date
Kind regards
Farhan