convert julian 5 digit number to regular date

General
Enthusiast

convert julian 5 digit number to regular date

I have the following field with julian numbers:

Date_Created

14304

14365

and so on.

I used the following formula

SELECT  CAST(CAST(Date_Created AS CHAR(7)) AS DATE FORMAT 'yyddd') FROM US_DWRP_TMO_R01.TDAIMFO

However, the returned date was in the wrong year (1900), for example:

10/31/1914, 02/27/1915

these dates should have been 2014 and 2015

any idea how to fix this?

Tags (1)
8 REPLIES
Junior Contributor

Re: convert julian 5 digit number to regular date

This behaviour is controlled by a global setting (CenturyBreak in dbccontrol).

Simply add '20' before casting:

CAST('20'||trim(Date_Created) AS DATE FORMAT 'yyyyddd')

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. 

As i am facing this issue after MLOAD step. 

 

Junior Contributor

Re: convert julian 5 digit number to regular date

What is 15947 supposed to return?

NULL or a date (which)?

 

 

Senior Apprentice

Re: convert julian 5 digit number to regular date

Hi,

Depends on what you mean by 'handle'.

 

From what you've said you're hitting a problem after the MLOAD process, so during some SQL.

 

If you want to avoid those rows then you could:

WHERE col-name MOD 1000 BETWEEN 1 and 366

Any rows with 947 as the last three digits will fail this test and will not be processed. You could then run anotehr query to copy 'failing' rows into your own error table.

Or you could use the 'error table' feature to automatically copy such rows. That might be easier.

 

If you're loading these values directly into a DATE column then I think the MLOAD process can trap that for you and will write the data to it's own error table.

 

If you want to keep those rows but want to change these values to something else (NULL?) then you could:

CASE 
WHEN col-name MOD 1000 BETWEEN 1 and 366 THEN do-whatever
ELSE NULL
END

Just be aware that the above code is always assuming that the maximum number of days in a year is 366. You'll need to add in leap-year logic for yourself.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

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?
Highlighted
Senior Apprentice

Re: convert julian 5 digit number to regular date

I agree that days > 366 do not make sense.

 

But first, what do you want to happen if you receive data like this.

 

Do you want these rows ignored?

Do you want them in a separate table?

Do you want the value stored as NULL - or another value?

 

Once you have said what you want to happen then we might be able to advise how to achieve that.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: convert julian 5 digit number to regular date

Hi,

 

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...)

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: convert julian 5 digit number to regular date

More likely, 15947 is a "Julian day number" offset from some starting epoch rather than a YYDDD date. As Dieter said, you need to know what date this number is supposed to represent.

For example, starting from the Unix epoch date 1970-01-01 and adding 15947 days would be 2013-08-30.