I agree that to be able to "tweak", in this context, has not much use (e.g.
you want day 1 to be sunday).

But if you want to use SQL for everything if there is some function
provided, then why not throw RPG out altogether and program everthing in
SQL/PL?

It's not the "preprocessor" machinery, it's having all kinds of
dependencies etc. For example you need to change the member type from RPGLE
to SQLRPGLE, because you need to do some date math. When the program runs,
the SQL runtime machinery is also initialized, just for some date maths,
which in every other language you don't need to resort to embedded SQL
Maybe it's optimzed that if you only call some functions it has no
overhead, but i don't know, etc..

Use SQL for DB tasks, use RPG for the rest.







Use SQL

On Wed, Dec 3, 2014 at 2:07 PM, <rob@xxxxxxxxx> wrote:

I will agree with you that you don't need the SQL preprocessor machinery
just to do some date calcs.
But if you already have it, then why not use it?
Tweakable? Might be useful if you discover that IBM has a bug, but if
they are truly falling outside of ISO specs then they should fix that
posthaste. But if your definition of tweakable is that you want to
combine the first Monday after the quarter into Tuesday and report them
both as day of week 2 then I suppose that may hold some water. (Granted,
if I saw that logic it might be hard for ME to hold water.)

On the bright side, you could show your management how much the purchase
of the SQL option would simplify code. That is one shining example.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: John E <whattssonn@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 12/03/2014 07:37 AM
Subject: Re: Get Start Date of a Week Number
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



You don't need the SQL preprocessor machinery, just to do some date
calcs.

And it's tweakable.

On Wed, Dec 3, 2014 at 1:29 PM, <rob@xxxxxxxxx> wrote:

And is that any easier to understand or trust than

p IsoWkdayNum b export
d IsoWkdayNum pi 10i 0
d date d const

d d s 10i 0

/free

exec sql values dayofweek_iso(date) into :d;

return d;

/end-free
p e


p IsoWeekNum b export
d IsoWeekNum pi 10i 0
d date d const

d w1 s 10i 0

/free

exec sql values week_iso(date) into :w1;

return w1;

/end-free
p e


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: John E <whattssonn@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 12/03/2014 07:17 AM
Subject: Re: Get Start Date of a Week Number
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



From http://nl.wikipedia.org/wiki/Weeknummer (which is in dutch) :

To calculate the ISO weekday (1=monday) for a given date:

p IsoWkdayNum b export
d IsoWkdayNum pi 10i 0
d date d const

d d s 10i 0

/free

d = %diff(date:d'1970-01-04':*d);

if d < -6;
return 7 - %rem(%abs(d) - 7 : 7);
elseif d < 0;
return 7 + d;
endif;

d = %rem(d : 7);

if d = 0;
return 7;
endif;

return d;

/end-free
p e


To calculate the ISO weeknumber for a given date (using the previous
IsoWkdayNum) :
The year must be from 1584 to 9998, else it returns 0.

p IsoWeekNum b export
d IsoWeekNum pi 10i 0
d date d const

d y s 10i 0
d m s 10i 0
d d s 10i 0
d w1 s 10i 0
d w2 s 10i 0
d wd1 s 10i 0
d wd2 s 10i 0
d wd3 s 10i 0
d wd4 s 10i 0
d wd5 s 10i 0
d x s 10i 0

/free

// http://nl.wikipedia.org/wiki/Weeknummer --------------------

y = %subdt(date:*y);

if y < 1584
or y > 9998;
return 0;
endif;

m = %subdt(date:*m);
d = %subdt(date:*d);

// UK02
w1 = %div(%diff(date:%date(y*10000+104:*iso):*d):7);

// UK03
wd1 = IsoWkdayNum(date);

// UK04
wd2 = wd1 - %rem(%diff(date:%date((y-1)*10000+104:*iso):*d):7);
if wd2 < 1;
wd2 = wd2 + 7;
endif;

// UK06
w2 = %div(%diff(%date((y-1)*10000+1231:*iso)
:%date((y-1)*10000+104:*iso):*d)
:7);

// UK07
wd3 = wd1 - %rem(%diff(date:%date((y-1)*10000+1231:*iso):*d):7);
if wd3 < 1;
wd3 = wd3 + 7;
endif;

// UK08
x = %diff(date:%date(y*10000+104:*iso):*d);
if x < 0;
wd4 = wd1 - x;
if wd4 > 7;
wd4 = wd4 - 7;
endif;
else;
wd4 = wd1 - %rem(x:7);
if wd4 < 1;
wd4 = wd4 + 7;
endif;
endif;

// UK09
wd5 = wd1 + %rem(%diff(%date((y+1)*10000+104:*iso):date:*d):7);
if wd5 > 7;
wd5 = wd5 - 7;
endif;

// UK11
if m = 1
and d >= 1
and d <= 3;
if wd1 < wd4;
return 1;
else;
// UK10
if wd3 < wd4;
return 1;
elseif wd3 < wd2;
return w2 + 2;
endif;
return w2 + 1;
endif;
elseif m = 12
and d >= 29
and d <= 31
and wd1 < wd5;
return 1;
elseif wd1 < wd4;
return w1 + 2;
endif;

return w1 + 1;

/end-free
p e


The code above is tested, and is correct.


On Wed, Nov 26, 2014 at 3:52 PM, Brian <belstsrv@xxxxxxxxx> wrote:

Hello All,

I have the need to get the date of the first day of the week based on
a
week number. I've looked thorough lots of date functions and can't
seem
to
see a way to get the date of the first day of the week for a
particular
week number. Maybe I need a multi-step process, but I am just
struggling
to see it.

The date can be based off Sunday or whatever arbitrary day could be
considered as the first day of the week, if that helps.

The data is planning data so I really just need to show it as a
quantity
along with a date, but the date needs derived from a week number.

As an example, if I had week number of 1, the date might be
12/29/2014,
week 2 would give me 1/5/2015 and so on.

Can anyone provide some suggestions on a method to get to a date based
off
the week number?

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


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


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


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


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



This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].