RE: Week Number Coding

[rob@xxxxxxxxx]

Now, I have a question.  I can create my own SQL functions, and have done
so.  Doing so requires that I create a service program and the judicious
use of the CREATE FUNCTION sql statement.  Granted WEEK and WEEK_ISO are
not the same as user defined functions.  Much like writing your own
subprocedure is not the same as a bif - even though the results may be.
But anyone figure out if these two functions run out of a service program,
and if so, which one?  With this knowledge we could call that service
program from our RPG and get what we want.

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



                    rob@dekko.com
                    Sent by:                To:     rpg400-l@midrange.com
                    rpg400-l-admin@mi       cc:
                    drange.com              Fax to:
                                            Subject:     RE: Week Number Coding

                    02/18/2002 01:01
                    PM
                    Please respond to
                    rpg400-l







Damn, you're good.
MYDATE    WEEK_ISO ( MYDATE )
01/01/00                52
01/02/00                52
01/03/00                 1
01/04/00                 1
01/01/02                 1
01/07/02                 2
01/06/02                 1

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



                    "Mangavalli,
                    Ramanujam"              To:
"'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
                    <RamM@Mvmills.com       cc:
                    >                       Fax to:
                    Sent by:                Subject:     RE: Week Number
Coding
                    rpg400-l-admin@mi
                    drange.com


                    02/18/2002 12:55
                    PM
                    Please respond to
                    rpg400-l






Rob,

What are the results when you use the WEEK_ISO(date) function?



-----Original Message-----
From: rob@dekko.com [mailto:rob@dekko.com]
Sent: Monday, February 18, 2002 12:48 PM
To: rpg400-l@midrange.com
Subject: Re: Week Number Coding



It is not ISO compliant.  For example:
select mydate,week(mydate)
from rob/a7
will return:
MYDATE    WEEK ( MYDATE )
01/01/00               1
01/02/00               2
01/03/00               2
01/04/00               2
01/01/02               1
01/06/02               2
01/07/02               2

Two problems:
1)  01/01/00 was on a Saturday.  Thus the first week began with Monday
01/03/00.
2)  Sunday's are the last day of a week, not the first.


But maybe this will be 'good enough', or 'what the requestor intended' for
the project.

But, who wants to rock the boat and get IBM to get their SQL to be ISO
compliant, or did ISO bend the rules for week within a SQL function?  Or
would the populace be more upset if it worked as ISO intended, versus how
we normally think?

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



                    "Marc Cras"
                    <MCRAS@mail.gates       To:     <rpg400-l@midrange.com>
                    .com>                   cc:
                    Sent by:                Fax to:
                    rpg400-l-admin@mi       Subject:     Re: Week Number
Coding
                    drange.com


                    02/18/2002 11:15
                    AM
                    Please respond to
                    rpg400-l






Sorry,

WEEH( date) should be WEEK(date)

MarcC

>>> MCRAS@mail.gates.com 18/02/02 17:00:37 >>>
If you can use SQl , there a scalar function called WEEH( date ) .
This will do the trick. Their was an example in the Iseries News issue of
October 2001.


Marc Cras

Gates Europe N.V.
Belgium

Tel : + 32-(0)53-762 756

E-mail : mcras@gates.com






_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.





_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.