Re: SQL: converting from 8,0 to a date -- MIDRANGE-L

Rob

Your question got me looking - as usual. The internal representation is
called a Scaliger (or Julian) number - not the same as *JUL. The formula
for calculating this number (as modified by IBM to start on Jan 1, 0001) is

jd = ( 1461 * ( y + 4800 + ( m - 14 ) / 12 ) ) / 4 + ( 367 * ( m - 2 - 12 *
( ( m - 14 ) / 12 ) ) ) / 12 - ( 3 * ( ( y + 4900 + ( m - 14 ) / 12 ) / 100
) ) / 4 + d - 32075 - 1721425

This integer can be used in a date() function in SQL and get it right. But
this seems a lot harder - you still need to extract year, month, and day
integers.

It was kind of fun, though, to run the following:

select date(( 1461 * (2002+ 4800 + ( 12- 14 ) / 12 ) ) / 4 +
( 367 * ( 12- 2 - 12 * ( ( 12- 14 ) / 12 ) ) ) / 12 -
( 3 * ( (2002+ 4900 + ( 12- 14 ) / 12 ) / 100 ) ) / 4 +
25- 1753500)        from qsys2/QSQPTABL

BTW, doesn't it seem that there should be minimal change impact, or need
for change? And SQL functions and procedures can be overloaded, so a
parmater change could be handled, I bet.

Happy holidays

Vern

At 02:39 PM 12/23/2002 -0500, you wrote:

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Had someone here ask me how to convert a date stored in a number (@#$%ing
BPCS) as 8,0 YYYYMMDD, within a SQL statement.  Feeling a little pinched
for time I threw in:
SELECT SCH06, DATE(SUBSTR(DIGITS(SCH06),5,2) || '/' ||
                   SUBSTR(DIGITS(SCH06),7,2) || '/' ||
                   SUBSTR(DIGITS(SCH06),1,4))
FROM EDIMFGCD/PP850D04

While functional, I looked for something better.  CAST doesn't work for
numbers to date.  I thought of creating a UDF based on our internal
dateedit program.

Then I whipped up the following instead:
CREATE FUNCTION NbrToDate
 (Nbr Decimal (8,0))
 Returns Date
 Language SQL
 Returns Null On Null Input
Begin
 Declare WorkDate Char (10);
 Declare ParmDate Date;
 Set WorkDate = substr(Digits(Nbr),5,2) || '/' ||
                substr(Digits(Nbr),7,2) || '/' ||
                substr(Digits(Nbr),1,4);
 Set ParmDate = Date(WorkDate);
 Return ParmDate;
End -- NbrToDate

Now I can do this:
SELECT SCH06, NbrToDate(sch06)
FROM EDIMFGCD/PP850D04

The person had the other method in use already.  Also, they felt that
using the function might cause problems if ERP, and accounting both
started using it and then one or the other wanted to change the function.
Then you would have to figure out an impact of change.  They felt that
having to modify all code containing the conversion manually would be
better.

Can anyone think of a better way to convert the date?

Rob Berendt
--