Week number retrieval
Bert de Vries
Anyone know of a subroutine to extract a weeknumber and day of the week from
a given date?
Example: Jan 8, 2000 gives year 2000, week 02 day 02 (when weeks starts on
sunday)
Weekcalculation is a real challence, because the start of week 1 of a random
year depends on several parameters. It has something to do with the day jan
1 is on. (if jan 1 = thursday then ..... etc)
Anyone?
--
Met vriendelijke groeten,
Bert de Vries
bdevri...@rbk.nl
Pobody is Nerfect ;-)
Charles R. Pence
> Anyone know of a subroutine to extract a weeknumber and day of the week from
> a given date?
> Example: Jan 8, 2000 gives year 2000, week 02 day 02 (when weeks starts on
> sunday)
> Weekcalculation is a real challence, because the start of week 1 of a random
> year depends on several parameters. It has something to do with the day jan
> 1 is on. (if jan 1 = thursday then ..... etc)
> Anyone?
The SQL: > select week(current date),dayofweek(current date)
from qsys2/qsqptabl
Replace "current date" with the appropriate date value.
Regards, Chuck
All comments provided "as is" with no warranties of any kind whatsoever.
elb...@cmfurniture.com
Paul Cunnane
<3A59FA61...@vnet.ibm.com>:
> The SQL: > select week(current date),dayofweek(current date)
> from qsys2/qsqptabl
> Replace "current date" with the appropriate date value.
Cool! Question: what is the rule to determine week 1? (week containing
the first Thursday, etc)
--
Paul __|__
-----(o)-----
Get DeCSS: http://decss.cyberspace.cz/
Bert de Vries
I do have some problems with this. Could you please clarify a bit more.
I tried the following:
STRSQL
SELECT WEEK('01012000') FROM QSYS2/QSQPTABL
SELECT WEEK('010100') FROM QSYS2/QSQPTABL
SELECT WEEK('20000101') FROM QSYS2/QSQPTABL
SELECT WEEK('000101') FROM QSYS2/QSQPTABL
with quotes, dubbel quotes, no quotes at all, dateseperators, no seperators,
with DAYOFWEEK and without, *DMYY, *YYMD, *DMY, *YMD, etc: all resulting in
SQL0171 (Argument 1 of function WEEK not valid)
I'm running OS/400 V4R4M0
How about something like this in RPGIII?
(embedded SQL?)
Thanks
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"Charles R. Pence" <crp...@vnet.ibm.com> schreef in bericht
news:3A59FA61...@vnet.ibm.com...
Bert de Vries
has the same number of weeks (sometimes 52, sometimes 53). With your
solution there is always the same number. Besides that, sometimes jan 01 is
in week 53 of the year before.
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
<elb...@cmfurniture.com> schreef in bericht
news:3a59fd30...@news.abts.net...
Barry Wright
SELECT WEEK(DATE('01012001')), DAYOFWEEK(DATE('01012001')) FROM
QSYS2/QSQPTABL
You can also use CURDATE() to get the current date if desired.
Barry
"Bert de Vries" <ma...@bdevr.demon.nl> wrote in message
news:978984081.21105....@news.demon.nl...
elb...@cmfurniture.com
My only other thought would be to create a calendar file with a entry
for everyday of the year. Containing month, day, year, day number, and
week number in it.
Its not the way I would prefer to do it. But its the way a lot of
shops do it.
Bert de Vries
BUT..........
Is this function accurate?
Try 31-12-00 (dec 31, 2000)
On my system it gives week 54 ??????? (should be 52, according my calender)
Try 31-12-01 (dec 31, 2001)
gives week 53 ?????? (should be 1)
Somehowe i have to code '-' as date seperator, otherwise this function
doesn't work. So my statement would be:
SELECT WEEK(DATE('31-12-00')), DAYOFWEEK('31-12-00')) FROM QSYS2/QSQPTABL
I see this function uses Sunday as start of the week. How about another day?
Monday is commonly used in the Netherlands. This would influence
weeknumbering, right?
Another question, how about RPGIII. I have to use the weeknumber and day of
the week in a RPG Program. I know there's something like embedded SQL. Do
you have any info on this?
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"Barry Wright" <barry....@tasb.org> schreef in bericht
news:93d869$jbc$1...@news.tamu.edu...
Bert de Vries
As you stated a calender file could do the trick, but isn't a nice solution.
I would like to use some calculations. It can be done. I'm just curious how.
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
<elb...@cmfurniture.com> schreef in bericht
news:3a5a234d...@news.abts.net...
Charles R. Pence
> crp...@vnet.ibm.com (Charles R. Pence) wrote in
> <3A59FA61...@vnet.ibm.com>:
> > The SQL: > select week(current date),dayofweek(current date)
> > from qsys2/qsqptabl
> > Replace "current date" with the appropriate date value.
>
> Cool! Question: what is the rule to determine week 1? (week containing
> the first Thursday, etc)
Beyond the information presented at the following URL, I have no more info:
http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rbafzmst61.htm#Header_465
For convenience, the comments are included here:
Syntax: >>-WEEK--(--expression--)----------------------------------><
The WEEK function returns an integer between 1 and 54 which represents the
week of the year. The basic accounting calendar is used. The week starts
with Sunday, and January 1 is always in the first week.
The argument must be a date or a timestamp.
The result of the function is a large integer. If the argument can be null,
the result can be null; if the argument is null, the result is the null value.
Charles R. Pence
>
> Thanks for your comment.
> I do have some problems with this. Could you please clarify a bit more.
> I tried the following:
> STRSQL
> SELECT WEEK('01012000') FROM QSYS2/QSQPTABL
> SELECT WEEK('010100') FROM QSYS2/QSQPTABL
> SELECT WEEK('20000101') FROM QSYS2/QSQPTABL
> SELECT WEEK('000101') FROM QSYS2/QSQPTABL
>
> with quotes, dubbel quotes, no quotes at all, dateseperators, no seperators,
> with DAYOFWEEK and without, *DMYY, *YYMD, *DMY, *YMD, etc: all resulting in
> SQL0171 (Argument 1 of function WEEK not valid)
> I'm running OS/400 V4R4M0
>
> How about something like this in RPGIII?
> (embedded SQL?)
For best results, always represent date character strings using *ISO
with the form: 'YYYY-MM-DD'
As such: SELECT WEEK('2001-01-08')
Charles R. Pence
>
> Bert de Vries wrote:
> >
> > Thanks for your comment.
> > I do have some problems with this. Could you please clarify a bit more.
> > I tried the following:
> > STRSQL
> > SELECT WEEK('01012000') FROM QSYS2/QSQPTABL
> > SELECT WEEK('010100') FROM QSYS2/QSQPTABL
> > SELECT WEEK('20000101') FROM QSYS2/QSQPTABL
> > SELECT WEEK('000101') FROM QSYS2/QSQPTABL
> >
> > with quotes, dubbel quotes, no quotes at all, dateseperators, no seperators,
> > with DAYOFWEEK and without, *DMYY, *YYMD, *DMY, *YMD, etc: all resulting in
> > SQL0171 (Argument 1 of function WEEK not valid)
> > I'm running OS/400 V4R4M0
> >
> > How about something like this in RPGIII?
> > (embedded SQL?)
>
> For best results, always represent date character strings using *ISO
> with the form: 'YYYY-MM-DD'
> As such: SELECT WEEK('2001-01-08')
Correction: SELECT WEEK(DATE('2001-01-08'))
Charles R. Pence
>
> Thank you for your comment.
>
> As you stated a calender file could do the trick, but isn't a nice solution.
> I would like to use some calculations. It can be done. I'm just curious how.
Actually calendar files are extremely robust; able to account for situations
which are not part of any "standard" calendars.
SJ Lennon
A calendar function, with links to more info on the ISO standard:
http://personal.ecu.edu/mccartyr/isowdcal.html
An algorithmn, possibly more than you need to know:
http://personal.ecu.edu/mccartyr/ISOwdALG.txt
The ISO 8601 date definition:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html, plus an extra from this
page:
"In commercial and industrial applications (delivery times, production
plans, etc.), especially in Europe, it is often required to refer to a
week of a year. Week 01 of a year is per definition the first week that
has the Thursday in this year, which is equivalent to the week that
contains the fourth day of January. In other words, the first week of a
new year is the week that has the majority of its days in the new year.
Week 01 might also contain days from the previous year and the week
before week 01 of a year is the last week (52 or 53) of the previous
year even if it contains days from the new year. A week starts with
Monday (day 1) and ends with Sunday (day 7). For example, the first week
of the year 1997 lasts from 1996-12-30 to 1997-01-05 and can be written
in standard notation as
1997-W01 or 1997W01
The week notation can also be extended by a number indicating the day of
the week. For example, the day 1996-12-31, which is the Tuesday (day 2)
of the first week of 1997, can also be written as
1997-W01-2 or 1997W012"
Sam
"Bert de Vries" <bdevri...@rbk.nl> wrote in message
news:978951199.5262....@news.demon.nl...
Thomas
For one variation, try this -- WEEK(date('01/01/2000')) -- assuming that
"/" is a valid date separator for you.
Tom Liotta
--
Tom Liotta, AS/400 Systems Programmer
The PowerTech Group, Inc.; http://www.400security.com
...and for you auto-things out there (NOT for people):
<a href="http://www.monkeys.com/cgi-bin/wpoison/wpoison.cgi">look!</a>
Sent via Deja.com
http://www.deja.com/
Charles R. Pence
> <<SNIP>>
> Is this function accurate?
> Try 31-12-00 (dec 31, 2000)
> On my system it gives week 54 ??????? (should be 52, according my calender)
> Try 31-12-01 (dec 31, 2001)
> gives week 53 ?????? (should be 1)
>
> Somehowe i have to code '-' as date seperator, otherwise this function
> doesn't work. So my statement would be:
> SELECT WEEK(DATE('31-12-00')), DAYOFWEEK('31-12-00')) FROM QSYS2/QSQPTABL
>
> I see this function uses Sunday as start of the week. How about another day?
> Monday is commonly used in the Netherlands. This would influence
> weeknumbering, right?
>
> Another question, how about RPGIII. I have to use the weeknumber and day of
> the week in a RPG Program. I know there's something like embedded SQL. Do
> you have any info on this?
There isn't a second operand of the WEEK function to effect specification
of the desired start day. If that is required, then additional calculations
about the first day of the year and its DAYOFWEEK would likely be required
to effect a revision to the pre-defined result of WEEK. Using SQL you'd
likely be required to effect the result using the CASE.
Example: Set the host variable WEEK_VAR (int) to the week of the date:
SELECT WEEK( DATE_FIELD_OR_LITERAL )
INTO :WEEK_VAR
FROM The_File
WHERE ...
For date related matters, it'd be best to use ILE RPG.
Paul Nicolay
The official algorithm is published as DIN 1355 Blatt1 / ISO/R 2015-1971
A program can be written based on that (Thomas Raddatz once wrote such one).
In addition, it is true that the first of January can be in the last week of
the previous year ! The mentioned WEEK function (in another posting) is
therefore incorrect.
Groetjes,
Paul
------------
Bert de Vries wrote in message
<978989253.24850....@news.demon.nl>...
Thank you for your comment.
As you stated a calender file could do the trick, but isn't a nice solution.
I would like to use some calculations. It can be done. I'm just curious how.
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
The contents of this message express only the sender's opinion.
This message does not necessarily reflect the policy or views of
my employer, Merck & Co., Inc. All responsibility for the statements
made in this Usenet posting resides solely and completely with the
sender.
Bert de Vries
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"Charles R. Pence" <crp...@vnet.ibm.com> schreef in bericht
news:3A5A5B0E...@vnet.ibm.com...
Bert de Vries
I believe when the definition is clear, an application could be made of it.
I'll have a go at it.............
THX.
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"SJ Lennon" <no...@none.com> schreef in bericht
news:%Ct66.4344$UQ5....@eagle.america.net...
Bert de Vries
How about the 'strange' results i got for dec 31, 2000 and dec 31, 2001?
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"Charles R. Pence" <crp...@vnet.ibm.com> schreef in bericht
news:3A5A8F5D...@vnet.ibm.com...
Bert de Vries
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"Charles R. Pence" <crp...@vnet.ibm.com> schreef in bericht
news:3A5A8F5D...@vnet.ibm.com...
Bert de Vries
Only one thing comes to mind. While building the file i still have to
retrieve somehow the weeknumbers to complete this file. In the Netherlands
there is a saying about this: wat was er eerst; de kip of het ei. In english
this would be something like: What existed first; the chicken or the egg.
I could do some manual input, but this is not why we bought a computer in
the first place. Besides that: we are supporting several customers with
software. In this software the weeknumber is required. We will not ask our
customers to maintain the calender database. So the calender database should
be 'self supporting'. See my point?
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"Charles R. Pence" <crp...@vnet.ibm.com> schreef in bericht
news:3A5A5BF1...@vnet.ibm.com...
Grzegorz Goryszewski
Charles R. Pence napisał(a) w wiadomości:
<3A5A527A...@vnet.ibm.com>...
Sorry for annoing, but what's wrong with my AS/400 V4R4? When I run "select
week(current date),dayofweek(current date)
from qsys2/qsqptabl" I had " QSQPTABL in QSYS2 type *FILE not found"
. Regards.
Grzegorz Goryszewski
--
Grzegorz Goryszewski
s...@domar-sc.com.pl
Paul Cunnane
<978989253.24850....@news.demon.nl>:
>As you stated a calender file could do the trick, but isn't a nice
>solution. I would like to use some calculations. It can be done. I'm
>just curious how.
For an application we are developing here, I used the ILE bindable APIs
(CEE*). The CEEDAYS API converts a date to lillian, which is essentially a
date serial number. CEEDYWK returns the day of the week of a lillian date,
with (again) Sunday being day 1. Based on week 1 being the week containing
the first Thursday of the year, the week number can be calculated for any
date of the year.
Carsten Flensburg
During our Y2K-conversion project I wrote some modules to handle the
conversion between dates and week/day-numbers - they f.x. allow you to set
day 1 to Sunday or Monday - you can have a copy if you're interested.
Best regards,
Carsten Flensburg
"Bert de Vries" <ma...@bdevr.demon.nl> wrote in message
news:979028558.10749....@news.demon.nl...
max_c...@my-deja.com
"Bert de Vries" <bdevri...@rbk.nl> wrote:
> Hi,
>
> Anyone know of a subroutine to extract a weeknumber and day of the
week from
> a given date?
> Example: Jan 8, 2000 gives year 2000, week 02 day 02 (when weeks
starts on
> sunday)
> Weekcalculation is a real challence, because the start of week 1 of a
random
> year depends on several parameters. It has something to do with the
day jan
> 1 is on. (if jan 1 = thursday then ..... etc)
> Anyone?
>
> --
> Met vriendelijke groeten,
> Bert de Vries
> bdevri...@rbk.nl
> Pobody is Nerfect ;-)
>
Hi,
Try this statement
select week(date('01/09/2001')),dayofweek(date('01/09/2001'))
from qsys2/qsqptabl
The problem here is that the date has to be in American format ie Month
Day Year.
Also it does not work with ISO dates.
Hope this is of help
Max
elb...@cmfurniture.com
the week, a number of the day of the week (0=Sunday, 1=Monday, etc).
And has a parm to adjust the date forward or backward.
In it I all my calculations are based on the julian date. No date
function opcodes are used, its all math. It probably could be modified
to return the week number of the year.
If you would like the code I'll give you a copy. I just don't have the
time to figure out the necessary code changes.
I would think by returning the number of the day of the week for Jan.
1st (say 3 if the year started on a Wednesday). Which this code
already does and adjusting the julian date plus or minus by this
offset and then dividing by 7 it could possibily return the correct
week number or any calendar date.
Hence my original post about julian date divided by 7. With the
appropriate days offset it should work. But like I said I have not had
any time to think about it.
If you want the code let me know.
PW Botha
holidays are very useful and can be flagged on the file.
It supported day number, week number month number and other fields. Those
days noe subdur and adddur's were available so you had to calculate days
between dates etc.
"Bert de Vries" <ma...@bdevr.demon.nl> wrote in message
news:979028557.10749....@news.demon.nl...
Bert de Vries
Thanks a lot
--
Met vriendelijke groeten
Bert de Vries
ma...@bdevr.demon.nl
"Carsten Flensburg" <Carsten....@novasol.dk> schreef in bericht
news:3a5b0a8e$0$3194$73be...@news.dk.uu.net...
Thomas Raddatz
for everyone who is interested in the program. The program looks a bit
confusing since it is almost an 1:1 translation of an old Fox/Pro routine.
However it works.
http://www.online-club.de/~thomas.raddatz/freeware/getweek.zip
Thomas.
Paul Nicolay schrieb:
--
*=====================================================*
e-mail Adresse:
Thomas....@Online-Club.de
Homepage:
http://www.tools400.de
*=====================================================*
Charles R. Pence
>
> How about the 'strange' results i got for dec 31, 2000 and dec 31, 2001?
>
> > > <<SNIP>>
> > > Is this function accurate?
> > > Try 31-12-00 (dec 31, 2000)
> > > Try 31-12-01 (dec 31, 2001)
> > > gives week 53 ?????? (should be 1)
The results do not even correlate to my copy of the IBM accounting calendar,
but they do seem consistent with the documenation; as noted I can not
comment beyond that per no more knowledge about the feature than that given
in the doc.
Charles R. Pence
> Sorry for annoing, but what's wrong with my AS/400 V4R4? When I run "select
> week(current date),dayofweek(current date)
> from qsys2/qsqptabl" I had " QSQPTABL in QSYS2 type *FILE not found"
Probably you have installed a secondary language on your system. Some
install code I have since corrected, would accidentally delete that file.
Please install SF64593 and let me know if that fixes the problem for you.
Charles R. Pence
>
> select week(date('01/09/2001')),dayofweek(date('01/09/2001'))
> from qsys2/qsqptabl
>
> The problem here is that the date has to be in American format ie Month
> Day Year.
>
> Also it does not work with ISO dates.
ISO requires '-' as separator... and the following works for me:
select week(date('2001-01-09')),dayofweek(date('2001-01-09'))
from qsys2/qsqptabl
Charles R. Pence
> The official algorithm is published as DIN 1355 Blatt1 / ISO/R 2015-1971
>
> A program can be written based on that (Thomas Raddatz once wrote such one).
> In addition, it is true that the first of January can be in the last week of
> the previous year ! The mentioned WEEK function (in another posting) is
> therefore incorrect.
From the doc I can only infer that the WEEK function's algorithm does not
match with that; not that it is incorrect. The doc:
The WEEK function returns an integer between 1 and 54 which represents
the week of the year. The basic accounting calendar is used. The week
starts with Sunday, and January 1 is always in the first week.
That doc clearly does not allow 1-Jan to be in the previous year.
Not to attempt to imply it was a good choice of implementation;;;
and to be sure, as I already pointed out, it does not even match my
copy of the IBM Rochester accounting/holiday calendar. What I do hope,
is that the implementation matches all supposed ANSI SQL implementations.
F.Hauptlorenz
The first week of a year, is where the 4th Januar is located.
I can also post a pascal source code how to calculate the week.
Perhaps you could add 4 days to your date and use the WEEK function?
Cheers,
Frank
On Tue, 09 Jan 2001 14:01:26 -0600, "Charles R. Pence" <crp...@vnet.ibm.com>
wrote:
Paul Cunnane
>> Sorry for annoing, but what's wrong with my AS/400 V4R4? When I run
>> "select week(current date),dayofweek(current date)
>> from qsys2/qsqptabl" I had " QSQPTABL in QSYS2 type *FILE not
>> found"
>
> Probably you have installed a secondary language on your system. Some
> install code I have since corrected, would accidentally delete that
> file. Please install SF64593 and let me know if that fixes the problem
> for you.
Alternatively, SELECTing this function from _any_ existing table on your
system will work.