Mainframe Date Convert

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

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

[ray.wurlod]

You should be able to use "%yyyy%ddd" as the date format string.

[suse_dk]

You should be able to use "%yyyy%ddd" as the date format string. ...

Needless to say that this would be a much better solution

[rajadommeti]

select to_char(CAST(TO_DATE (yourdate, 'yyyyddd') as timestamp),'yyyy-mm-dd') from yourtable;

[ray.wurlod]

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.