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

--
[ Picked text/plain from multipart/alternative ]
We have a wide variety of date formats resulting from too many expensive
cowboys doing overnight fixes to the data (read: consultants). The result is
that I'm fixing the data in Java or JavaScript for presentation. That way, my
SQL statements are clean, and the presentation is responsible for formatting
to the table.

Yes, I know it would be better to create a project to clean up the dates.
However, after the horrors of Y2K and budget crunches, that is a project I
would find hard to sell.

- Hank Heath

In a message dated 12/23/2002 4:04:03 PM Eastern Standard Time, rob@dekko.com
writes:


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