Graybeard52   2355 pts.  |   May 5 2009  12:23PM GMT

Here are some easy ways

EXEC SQL Set :DayOfWeekNum = DayOfWeek(:Checkdate) // Sunday is 1, Monday is 2, etc

EXEC SQL Set :DayOfWeekNum = DayOfWeek_ISO(:Checkdate) // Monday is 1, Tuesday is 2, etc

EXEC SQL Set :DayName = DAYNAME(:Checkdate);

WilsonAlano   2005 pts.  |   May 6 2009  5:18PM GMT

Hi,

Take a look.

DayofWeek=%abs(%rem(%diff(tDate:d’2001-12-16′:*d):7));//0=Sunday

Frmdeva   215 pts.  |   May 10 2009  6:28AM GMT

Hi Gray,
Can we use the same line wht you have mentioned directly in my SQLRPG.?

I will move the required date to Checkdate field.

Please clarify me…

BigKat   2480 pts.  |   May 11 2009  2:36PM GMT

Hi Wilson,

Yes, 0001-01-01 is a Monday, and this eliminates the negative date difference, which I believe also messes up the day of week value.

If you use a Sunday as reference, +6 days is a Saturday, but -1 days is a Saturday. The remainder of the difference divided by 7 is only valid for days GREATER than the reference. Which is one of the two main reasons I use 0001-01-01. (The other is that the weekend is >4)

Kevin aka “BigKat”

BigKat   2480 pts.  |   May 11 2009  2:50PM GMT

or I should say it is only valid for the same sign of the difference. That is dates greater than the the reference date always have one set of values, and dates less than the reference date always have another.

assuming Sunday reference date

d.o.w	>ref	<ref
SUN	0	-0
MON	1	-6
TUE	2	-5
WED	3	-4
THU	4	-3
FRI	5	-2
SAT	6	-1

Graybeard52   2355 pts.  |   May 11 2009  5:37PM GMT

>> Can we use the same line wht you have mentioned directly in my SQLRPG.? <<<

Frmdeva,
Yes. Its intended for use in SQLRPGLE, just put a /free line ahead of it (and /end-free after) if you are using fixed form code. . I haven’t checked obsolete versions of RPG, but it should work ok. The EXEC syntax might be a little different.

WoodEngineer   2150 pts.  |   May 11 2009  5:59PM GMT

Hi WilsonAlano,

Very nice code example for calculating date of week. I really like that the code is complete in a single line.

When I tired it with dates prior to 2001-12-16 it returned incorrect day numbers.
Am I missing something in your code?
As long as the date is later the 2001-12-16 all works just fine.

Cwc   3825 pts.  |   May 12 2009  1:54PM GMT

WoodEngineer, I use a similar method to WilsonAlano’s, and just adjust the result to account for an input date being prior to the reference date. I like using a formula where Sunday = 1 and Saturday = 7, so in the example below, I’m also adding 1.

By adjusting the result, any known Sunday reference date will work. So if the days’ difference between the two dates divided by 7 plus 1 is negative, then I add 7 and then use that result as an array index to look up the day names. (In this case, I don’t want the absolute value, since I need to test the result).
Example:

Day = %Rem( %Diff( Date: d’2001-12-16′: *Days) : 7) + 1 ;
IF Day < 1 ;
Day += 7 ;
ENDIF ;

BigKat   2480 pts.  |   May 12 2009  2:57PM GMT

just use 0001-01-01 as the reference date, and then ALWAYS 0 = MON, 1 = TUE, … 6 = SUN

BigKat   2480 pts.  |   May 12 2009  3:03PM GMT

Hi Gray and FrmDeva

just fyi for non-free format (and SQLRPG length names)

C/EXEC SQL
C+ Set :DOWNum = DayOfWeek(:Chkdte)
C/END-EXEC

WilsonAlano   2005 pts.  |   May 12 2009  7:23PM GMT

Hi,

To make work the same formula just change date ‘2001-12-06′ to ‘0001-01-07′. It will combine BigKat technique with mine.

Regards
Wilson

Teckgeck   170 pts.  |   Jun 2 2009  2:52PM GMT

just as a note - in answer to your original question - yes the CEEDYWK API will do this

set up the following prototype in your D specs

D CEEDYWK         Pr                  OpDesc
D  LilianDate                   10I 0 Const
D  DayOfWeekNbr                 10i 0
D  FeedBack                     12A   Options(*Omit)

then to use it just code

Ceedywk(LilianDate:DayOfWkN:*Omit);

the only drawback with this method is that it requires a lillian date for input - however you can use the CEEDAYS API to convert a date into lillian format if required