Re: convert julian 5 digit number to regular date

Hi Team,

Thanks for all your help so far. Appreciate it.

Quickly want to ask one tip when julian date (YYDDD) has somethink like this (15947) i.e. your days is greater than range 1-366. I belive DDD is 947 which is excedding the range. Can someone pls help how to handle this scenario. Or do we need to put it into error table. If yes, please suggest some solution.

Re: convert julian 5 digit number to regular date

I am not really sure this scneario. This is something which i am getting from source file. I belive these kind of scenario should be discarded but atleast it should not fail the process. Right now it is abending my job which i need to fix it. It doesn't make sense to get days greater than 366. To best of my knowledge it is exception case. Pls advise how we can handle it?

Re: convert julian 5 digit number to regular date

If you want to discard these rows, then perhaps you can do that in the MLOAD job.

The ".IMPORT" command includes the APPLY clause. That allows a WHERE condition.

If your 5-digit 'date' (e.g. 15947) is supplied as character data, then you'll probably need to define them as two fields, let's say:

.FIELD * julian_yy CHAR(2);

.FIELD * julian_day CHAR(3);

These will get loaded as two columns in the target table.

In the WHERE condition of the APPLY clause you could code:

"WHERE julian_day BETWEEN 1 and 366"

This will allow MLOAD to avoid loading rows with bad values. The job will not abend for this reason.

Once the data is in Teradata then you'll need to re-combine the two columns into a single DATE column. But you'll still need to ensure that you handle the leap years correctly - but that is probably easier to do using SQL.

(or of course you can get the source system to correct the data that it is sending to you...)