Hello ,
I use Data transfer from system iseries . Throgh sql i downloaded the data. By default the date format is julian date when i download , if i want the date format to calender date one standards table F00365 is there , through this table join we convert the juilan format to calendar format. The issue is if one date field is there , no issue . if more than one date fields is how we write the query to convert the both date field as calendar date format. My query mention below ,kindly help on this issue

SELECT F0311.RPAN8"CUST.NO",F0311.RPALPH"CUST NAME",F0311.RPSDOC"ORDER NO",F0311.RPSDCT"ORDER TY",F58421A.CCLGL1"CUST.PONO",F0311.RPDOC"INVOIC E NO" ,F0311.RPDCT"TYPE",F00365.ONDATE "INVOICE DATE",F0311.RPDDJ"DUE DATE",F0311.RPAG/1000"ORIGINAL AMT",F0311.RPAAP/100 "OPEN AMT"FROM P2DTAA/F0311 LEFT OUTER JOIN F58421A ON F58421A.CCDOCO = F0311.RPSDOC AND F58421A.CCDCTO = F0311.RPSDCT LEFT OUTER JOIN F00365 ON F00365. ONDTEJ = F0311.RPDIVJ WHERE AND (F0311.RPAAP > 0) AND (F0311.RPTRTC <> 'G')

Here i joined the table F0311 & F00365 joined fields are
F00365.ONDTEJ = F0311.RPSDCT and in the selection i put calender fleld is F00365.ONDATE.
The same function i want to use for F0311.RPDDJ also


The tables are used is F0311,F58421A,F00365
Help me

Suresh

You can convert julian date to calendar date by an expression, like this:

DATE('0001-01-01') + (julian_date - 1721426) DAYS

Hi ,

Thanks for valuable information, But i shows some error , the error message & SQL query was follows for your information

QUERY
SELECT ILLITM,ILMCU,ILDOC,ILDCT,(DATE('0001-01-01') + (ILDGL-1721426)DAYS),ILTRUM,ILTRQT FROM P2DTAA/F4111 WHERE ILMCU LIKE '%ITR' AND ILDCT = 'II' AND ILDGL >110266

Error Message

WBDB0099 - No more data is available for the stream fetch request

SQL0183 - Result of date or timestamp expression not valid.

Cause . . . . . : The result of an arithmetic operation is a date or timestamp that is not within the valid range of dates which are between 0001-01-01 and 9999-12-31. If the result is a date in the format YMD, MDY, DMY, or JUL then the year must be between 1940 and 2039. If this is a FETCH, embedded SELECT, SET or VALUES INTO, then the relative position of the host variable in the INTO clause is 5 and the host variable name is *N. Recovery . . . : Correct the arithmetic expression or the data that was being processed at the time the error occurred. If the date format is YMD, MDY, DMY or JUL and the result is not between 1940 and 2039, then specify USA, ISO, EUR, or JIS for the date format. The date format can be specified on the STRSQL or CRTSQLxxx commands or can be changed for the job by using the CHGJOB command. Try the request again.

KINDLY HELP ME

THanks in advance
Suresh

What is the value of ILDGL?
Is it really Julian date?

In your code, there is a phrase "AND ILDGL >110266".

If the value of ILDGL is Julian date,
ILDGL = 110267(lowest number which satisfy the condition) may be about February 7 4411 B.C.
(110267 days from January 1, 4713 B.C.(the start of the Julian date calendar)).

See JULIAN_DAY built-in function in SQL References(e.g. "DB2 9.7 for LUW SQL Reference, Volume 1" or "DB2 for i SQL Reference 7.1").