Re: SQL: converting from 8,0 to a date

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Durn!  I forgot about overloading SQL functions and/or procedures!

I couldn't see the need either for any change impact.  However people
years ago, (at least some ostrich's), couldn't see a need to change from 6
to 8 digit dates.  And using that example, (forgetting about overloading),
with some applications not migrated over the same hour I could see
problems.  And I choose to let that battle go.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Vern Hamberg <vhamberg@centerfieldtechnology.com>
Sent by: midrange-l-admin@midrange.com
12/23/2002 08:16 PM
Please respond to midrange-l

        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        Re: SQL:  converting from 8,0 to a date


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


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.