I have the following field with julian numbers:
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:
these dates should have been 2014 and 2015
any idea how to fix this?
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')
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.
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.
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.
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...)
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.