February 07, 2013, 02:17 PM
kumar27 [CLOSED] How to find second Sunday of the month
Hi
i have recruitment like this
Reports run manually in monthly wise
i have Date Filed(FLD_NAME/YYMD) ,from this Field i need to pull the data.
Report run on December month
Filter condition like second Sunday of December month to second Sunday of January month
WHERE FLD_NAME GE '2012/12/09' AND FLD_NAME LT '2013/01/12'
if Report run on January month
Filter condition like second Sunday of January month to second Sunday of February month
WHERE FLD_NAME GE '2013/01/13' AND FLD_NAME LT '2013/02/09'
i tried to find "second Sunday of Month" using below code
can any one help me please
Below code will find second Friday of the month
DEFINE FUNCTION SCNDSAT/A8YYMD (SEEDDATE/A8YYMD)
SEEDYYMD/YYMD = SEEDDATE ;
CALC1/YYMD = DATEMOV(SEEDYYMD, 'BOM') ;
YYMDEOW/YYMD=DATEMOV(CALC1, 'EOW') ;
CALC2/YYMD=YYMDEOW + 7;
SCNDSAT/A8YYMD = CALC2;
END
-RUN
-SET &TESTDATE = '20121201' ;
-SET &RES = SCNDSAT(&TESTDATE) ;
-TYPE &TESTDATE - &RES
This message has been edited. Last edited by: Kerry , February 15, 2013 12:04 PM February 07, 2013, 03:38 PM
Waz Here is a thought:
If the function returns the second friday, then just add two more days to get the sunday.
CALC2/YYMD=YYMDEOW + 9;
February 07, 2013, 05:09 PM
FrankDutch And what about the 3th Monday...
See my suggestion to change the function a bit.
But maybe some users should start with reading the manuals and attend some training.
We can't continue fishing for them....
February 07, 2013, 09:33 PM
kumar27 hi
i tried but its not giving correct date
quote:
Originally posted by Waz: Here is a thought: If the function returns the second friday, then just add two more days to get the sunday. CALC2/YYMD=YYMDEOW + 9;
February 07, 2013, 09:47 PM
Waz What date is it giving ?
Some extra info will help.
Also post "your" code, a working example.
February 07, 2013, 09:52 PM
Waz This calcs the sunday and also states the day of week.
DEFINE FUNCTION SCNDSAT/A8YYMD (SEEDDATE/A8YYMD)
SEEDYYMD/YYMD = SEEDDATE ;
CALC1/YYMD = DATEMOV(SEEDYYMD, 'BOM') ;
YYMDEOW/YYMD=DATEMOV(CALC1, 'EOW') ;
CALC2/YYMD=YYMDEOW + 9;
SCNDSAT/A8YYMD = CALC2;
END
-RUN
-SET &TESTDATE = '20121201' ;
-SET &RES = SCNDSAT(&TESTDATE) ;
-SET &DOW = DOWKL(&RES,'A12');
-TYPE &TESTDATE - &RES &DOW and I get
20121201 - 20121216 SUNDAY What do you get ?
February 07, 2013, 11:51 PM
kumar27 HI waz
this code working fine but some month when 1st day of the month start with Saturday or Sunday
then it giving 3rd week of Saturday or Sunday
but i want only second sunday only
for ex
ecember 2012 it should show (20121209 2nd sunday of the month) but it is showing as (20121216 -- 3rd sunday of the month)
can you help me how to solve this type of problem
when 1st day of the month start with Saturday or Sunday
Thanks
quote:
Originally posted by Waz:
This calcs the sunday and also states the day of week.
DEFINE FUNCTION SCNDSAT/A8YYMD (SEEDDATE/A8YYMD)
SEEDYYMD/YYMD = SEEDDATE ;
CALC1/YYMD = DATEMOV(SEEDYYMD, 'BOM') ;
YYMDEOW/YYMD=DATEMOV(CALC1, 'EOW') ;
CALC2/YYMD=YYMDEOW + 9;
SCNDSAT/A8YYMD = CALC2;
END
-RUN
-SET &TESTDATE = '20121201' ;
-SET &RES = SCNDSAT(&TESTDATE) ;
-SET &DOW = DOWKL(&RES,'A12');
-TYPE &TESTDATE - &RES &DOW and I get
20121201 - 20121216 SUNDAY What do you get ?
February 08, 2013, 04:24 AM
FrankDutch Kumar
Try to find out your self how this could work
Every day of the week has a number form 0 to 6
Friday is the 6
S 0
S 1
M 2
T 3
W 4
T 5
F 6
So in the formula you first find out what the first day of the month is.
Suppose that is a Monday, the number is 2
Between Friday and Monday there are (6-2)+1 days this is 5
Between Wednesday and Sunday there are (4-1)+1 days this is 3
If you want to find the second Friday or Thursday you have to change the formula a bit so it is
(6-2)+1+7 and so on
Or to make it look more mathematic
First day of the month is Fday
Day to be calculated is Cday
Number you want to find is N
Formula would be: (Fday - Cday +1) + (N-1)*7
I hope this does help a bit.