| View previous topic :: View next topic |
| Author |
Message |
sanjayr321
Beginner
Joined: 25 Sep 2006
Posts: 28
Topics: 15
|
 Posted: Mon Dec 04, 2006 11:48 am Post subject: find the Date of the month |
 |
|
Hai All,
I want to find the date for the following days using cobol pgm
a) first sunday of the current month
b) Second sunday of current month
c) second sunday of previous month
It should be find for each month to use in the select query..
Please let me know is there any possible logic..
Thanks in advance..
Sanjay.. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Dec 04, 2006 12:55 pm Post subject: |
|
|
sanjayr321,
try this code
| Code: |
01 WS-CURR-DATE PIC 9(08).
01 WS-CURR-DATE-R REDEFINES WS-CURR-DATE.
05 WS-CURR-YEAR PIC 9(04).
05 WS-CURR-MONTH PIC 9(02).
05 WS-CURR-DAY PIC 9(02).
01 WS-LAST-MON-FIRST-DATE PIC 9(08).
01 WS-LAST-MON-FIRST-DATE-R REDEFINES WS-LAST-MON-FIRST-DATE.
05 WS-LAST-YEAR PIC 9(04).
05 WS-LAST-MONTH PIC 9(02).
05 WS-LAST-DAY PIC 9(02).
01 WS-DIVIDE-ANSWER PIC S9(08) COMP.
01 WS-INTEGER PIC S9(08) COMP.
01 WS-REMAINDER PIC S9(04) COMP.
PROCEDURE DIVISION.
MOVE FUNCTION CURRENT-DATE (1:8) TO WS-CURR-DATE
PERFORM 1000-CURR-MONTH-SUNDAYS
MOVE FUNCTION CURRENT-DATE (1:8) TO WS-CURR-DATE
PERFORM 1500-LAST-MONTH-SUNDAYS
GOBACK
.
1000-CURR-MONTH-SUNDAYS.
MOVE 01 TO WS-CURR-DAY
MOVE WS-CURR-DATE-R TO WS-LAST-MON-FIRST-DATE
PERFORM 2000-GET-WEEKDAY
DISPLAY 'THE FIRST SUNDAY OF CURR MONTH:'
WS-LAST-MON-FIRST-DATE
ADD +7 TO WS-LAST-DAY
DISPLAY 'THE SECOND SUNDAY OF CURR MONTH:'
WS-LAST-MON-FIRST-DATE
.
1500-LAST-MONTH-SUNDAYS.
SUBTRACT 1 FROM WS-CURR-MONTH
IF WS-CURR-MONTH = 0
MOVE 12 TO WS-CURR-MONTH
SUBTRACT 1 FROM WS-CURR-YEAR
END-IF
MOVE WS-CURR-DATE-R TO WS-LAST-MON-FIRST-DATE
PERFORM 2000-GET-WEEKDAY
ADD +7 TO WS-LAST-DAY
DISPLAY 'THE SECOND SUNDAY OF LAST MONTH:'
WS-LAST-MON-FIRST-DATE
.
2000-GET-WEEKDAY.
COMPUTE WS-INTEGER = FUNCTION
INTEGER-OF-DATE(WS-LAST-MON-FIRST-DATE)
DIVIDE WS-INTEGER BY 7 GIVING WS-DIVIDE-ANSWER
REMAINDER WS-REMAINDER
ADD 1 TO WS-REMAINDER
EVALUATE WS-REMAINDER
WHEN 1
ADD +0 TO WS-LAST-DAY
WHEN 2
ADD +6 TO WS-LAST-DAY
WHEN 3
ADD +5 TO WS-LAST-DAY
WHEN 4
ADD +4 TO WS-LAST-DAY
WHEN 5
ADD +3 TO WS-LAST-DAY
WHEN 6
ADD +2 TO WS-LAST-DAY
WHEN 7
ADD +1 TO WS-LAST-DAY
END-EVALUATE
.
|
| Quote: |
It should be find for each month to use in the select query..
|
If your intention is to use it in select query then a simple sql like below will give you the desired results.
a) first sunday of the current month
| Code: |
SELECT DATE(NEXT_DAY(LAST_DAY(CURRENT DATE - 1 MONTH),'SUN'))
FROM SYSIBM.SYSDUMMY1;
|
b) Second sunday of current month
| Code: |
SELECT DATE(NEXT_DAY(LAST_DAY(CURRENT DATE - 1 MONTH) + 7 DAYS,'SUN'))
FROM SYSIBM.SYSDUMMY1;
|
c) second sunday of previous month
| Code: |
SELECT DATE(NEXT_DAY(LAST_DAY(CURRENT DATE - 2 MONTH) + 7 DAYS,'SUN'))
FROM SYSIBM.SYSDUMMY1;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
sanjayr321
Beginner
Joined: 25 Sep 2006
Posts: 28
Topics: 15
|
| Posted: Tue Dec 05, 2006 9:36 am Post subject: |
|
|
Thanks Kolusu...
its working fine, but i have few doubts on the select query..
can u explain little bit more on how the funtions of the NEXT_DAY, LAST_DAY and calculation on the current_date are working or provide some links on this information... |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Tue Dec 05, 2006 9:46 am Post subject: |
|
|
sanjayr321,
1. Click on "Quick Manuals" link on top of this page.
2. Scroll down to DB2 section
3. Click on "DB2 V7 SQL Reference" manual
4. Search for Next_day and learn more about next_day scalar function
5. Repeat item # 4 for explanation of other scalar functions.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
