Error: Invalid Julian day

[reachthiru]

Hi,

I have a simple job which loads data from sequential file to SQL Server. For this, I am using a sequential file stage and SQL Server stage respectively. The mapping is 1:1. I am reading all the fields as Varchar, but loading the data as per the requirement. The problem is when I am loading a NULL value to a field which is of datetime type, I am getting a fatal error 'Invalid Julian day'.

I have searched this forum and everybody says NULL can be inserted to a date field as long as it is defined as null. I defined my field as null only. Then I added a Transformer stage in between and tried to load a default value like below

Code: Select all

NullToValue(DSLink3.DATSL,'1989-01-02')

I got the same error. Then I tried the below one:

Code: Select all

OConv(IConv(NullToValue(DSLink3.DATSL,'1989-01-02'), 'D-YMD[4,2,2]'), 'D-YMD[4,2,2]') : ' 00:00:00'

But that line becomes red once I complete my editing. I don't know what's the error in it?

Now, I would like to know

1) How to insert a NULL date value in to a SQL Server date field?
2) Why the above conversion didn't parse?

Thanks all for your valuable comments to this.

[ray.wurlod]

1) SetNull()
2) Iconv() and Oconv() are only for server jobs, not parallel jobs.

[reachthiru]

Hi Ray,

Thanks for your prompt reply and wishes for reaching 10000 posts.

Probably I haven't phrased my first question properly. I don't want to insert a NULL value to a date field always. What I want is I am reading a field as Varchar and inserting the value to a datetime field in SQL server. The job should insert the value as it is, means if a date value is there, it should insert the date value or if it is NULL, it should insert NULL. How to do that?

I tried the following things in the mean time

1) Used the StringToTimeStamp function in the Transformer stage. Since SQL Server datetime fields is treated ast Timestamp in DS, I used this.
2) Used the SQL Convert() function in the user defined SQL.
3) Infact I also tried a hardcoded value like Convert(datetime,'2001-01-01') in the SQL statement. (Note that, I didn't remove the mapping for that field. Only in the user-defined SQL stmt, I have hard coded the value).

But I got the same error all the time.

Then I tried to read the field as Timestamp, instead of Varchar. But it gave me an error. I changed back to Varchar and set a column level null value. But this time I got a different run-time error:

APT_CombinedOperatorController,0: Caught unknown exception from runLocally().
Operator signalled one or more errors.

What could be the other alternative to this?

Thanks.