Support for large dates - say 5874897-12-31

[manish1005]

Hi,

I wish to insert records with large dates as in 5874897-12-31. The field can contain smaller dates as well say 101-12-31. The function date_from_string in datastage requires exact date format %yyyy-%%mm-%dd, but my data can vary from %yyy-%mm-%dd to %yyyyyyy-%mm-%dd.

Is there any way to convert such strings to date format? and also is there any upper limit on that? My database supports dates till year 5874897.


Another question is, is there any way to load dates with BC & AD values in them like - 4713-01-01 BC. My database supports it but I could not figure out how to make DataStage understand that this is a valid date. If I map varchar to date type I have to convert it manually using some function, date_from_string also does not seems to understand dates with BC, AD.

Please suggest.

[chulett]

Ok, let me be the first to ask - what database supports 'large dates' like that and why in the world would you want to use them?

date_from_string also does not seems to understand dates with BC, AD.

Please suggest.

Suggest you leave that bit off.

[manish1005]

>what database supports 'large dates' like that.
Well postgres database supports that, and i guess other datases would also support atleast dates with 5 digit years %yyyyy-%mm-%dd

>why in the world would you want to use them
why not, say I have a database of objects related to archeology(or may be even astronomy) which gives me dates from carbon dating of dinosaur bones. (instead of writing years, I wish to store it in date format with month/date as 01-01 and of course in BC era).

Moreover, instead of questioning *why* I have to provide a solution for *how* part

[chulett]

Just curiousity on my part is all.

No clue on the how. If no-one else pops in here having done that before, why not open a case with your official Support provider? Go back to the source and ask them if they can support dates of that nature.

[ArndW]

You might have trouble with variable-length date formatting in PX. How about keeping your date in a numeric (i.e. Julian) form and then using your database SQL to perform the to_date() conversion?

[ray.wurlod]

Standard ISO8601 only defines dates to 9999-12-31.

DataStage conforms to that standard, as do most (though clearly not all) commercial databases.

[manish1005]

Regarding ISO8601 standard, it allows negative dates for BCE. But when I tried to supply negative dates ( -1001-11-11) it gave error for the negative sign.


Julian format can be a workaround, but how do I convert dates in my source flat files to julian format?

[ArndW]

This might be an argument for a BASIC transform stage and the ICONV()/OCONV() functions.