Re: SQL dates and numeric columns

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hey Jim, wasn't there the old RPG chestnut of multiplying by 1000000 of 
a couple numbers in a data structure? Another "cute" trick, 
unmaintainable, and a dog as to performance.

I might have the exact details off, but it's no longer the think to do.

Cheers
Vern

On 9/5/2023 10:01 AM, Jim Oberholtzer wrote:
> The reason those calculations work is (presuming the fields are set to  6
> char) they cause arithmetic overflows and exceptions the system is smart
> enough to ignore.  While the code may be somewhat shorter, it's not
> intuitive, only the initiated would understand it, and it performs
> horribly.  While the SQL and several other methods of date management might
> not be as short, they perform better and in the end far more maintainable.
> None of my customers allow that type of date manipulation anymore.  That
> fad went out of style back in the very early 2000's.  While more verbose,
> time to use modern methods.
>
> --
> Jim Oberholtzer
> Chief Technical Architect
> Agile Technology Architects
>
>
> On Tue, Sep 5, 2023 at 9:42 AM Dave <dfx1@xxxxxxxxxxxxxx> wrote:
>
> > I'm surprised it's still so difficult to get a date from a numeric value
> > Concat gives me this horrendous code :
> >
> > date(
> >
> > concat(
> >
> >   concat(
> >
> >    concat(trim(char(Y)), '-'), concat(trim(char(M)), '-')
> >
> >         )
> >
> >       , trim(char(D)))) " MyDate  "
> >
> > I would have liked to have been able to use
> > Y* 10000 + M* 100 + J
> >
> >
> >
> > ---------- message ---------
> > From: Dave <>
> > Date: Fri, 1 Sept 2023 at 10:40
> > Subject: SQL dates and numeric columns
> > To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> >
> >
> > Hi,
> >
> >
> >
> > Trying to do date operations on a physical file where the date is coded in
> > 3 different numeric fields Y, M and D
> >
> >
> >
> > 1 -  I created a view, can I do better than this :
> >
> > date(trim(char(Y))!!'-'!!trim(char(M))!!'-'!!trim(char(D))) "MyDate"
> >
> >   ?
> >
> >
> > 2 – This works in an SQL session but the RPG compiler doesn’t seem to like
> > the ‘ !’ character. Any ideas ?
> >
> >
> >
> > Thanks
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> > questions.