Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to find second Sunday of the month

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] How to find second Sunday of the month
 Login/Join
 
kumar27
Member
posted
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,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 7 | Registered: January 09, 2013Report This Post
Waz
Expert
posted Hide Post
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;


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
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....




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
kumar27
Member
posted Hide Post
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;


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 7 | Registered: January 09, 2013Report This Post
Waz
Expert
posted Hide Post
What date is it giving ?

Some extra info will help.

Also post "your" code, a working example.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Waz
Expert
posted Hide Post
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
kumar27
Member
posted Hide Post
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 exBig Grinecember 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 ?


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 7 | Registered: January 09, 2013Report This Post
FrankDutch
Virtuoso
posted Hide Post
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.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to find second Sunday of the month

Copyright © 1996-2020 Information Builders