Page 1 of 1
Mainframe Date Convert
Posted: Thu Oct 27, 2011 3:48 am
by kanasai167
1959047
1963112
1951356
1967027
1958213
this is the value of the date, i think the first 4 digit is the year ? and the remaining 3 is day of the year ? If so , how should i make it into yyyy-mm-dd format?
is there any ways to do it ?
thanks.
Posted: Thu Oct 27, 2011 6:22 am
by suse_dk
I presume it is defined as a string in the source?
So, split the string 'YYYYint' into YYYY (where you change it to a date YYYY-01-01) and the int value
Convert the date into a julian date
Add the two int values together (I think you need to subtract 1)
Convert the new julian date to the date format of your desire.
Posted: Thu Oct 27, 2011 7:13 am
by ray.wurlod
You should be able to use "%yyyy%ddd" as the date format string.
Posted: Thu Oct 27, 2011 7:18 am
by suse_dk
ray.wurlod wrote:You should be able to use "%yyyy%ddd" as the date format string. ...
Needless to say that this would be a much better solution
Re: Mainframe Date Convert
Posted: Thu Oct 27, 2011 7:18 am
by rajadommeti
select to_char(CAST(TO_DATE (yourdate, 'yyyyddd') as timestamp),'yyyy-mm-dd') from yourtable;
Re: Mainframe Date Convert
Posted: Thu Oct 27, 2011 12:41 pm
by ray.wurlod
rajadommeti wrote:select to_char(CAST(TO_DATE (yourdate, 'yyyyddd') as timestamp),'yyyy-mm-dd') from yourtable;
Why do you think the source is a database (and what kind of database do you believe it to be)? Source data are coming from a mainframe, and its structure is defined in a COBOL file definition.