|
View previous topic :: View next topic
|
| Author |
Message |
rmd3003
New User
Joined: 03 Jul 2006
Posts: 55
|
|
|
|
Is there a way to check if current date equal first Friday of the month minus three days?
For example first Friday in October was 10/02, so my result would be 9/29.
For November first Friday is 11/06, so result is 11/03.
Thanks. |
|
| Back to top |
|
 |
pradeep_123
New User
Joined: 21 Nov 2007
Posts: 9
Location: bhubaneswar
|
|
|
|
hi,
First try to find out day using sql query
SELECT DAYOFWEEK(CURRENT_DATE)
INTO :WE-VAR
FROM SYSIBM.SYSDUMMY1
if WS-VAR = 6, then that day is friday.
you can the result by subtracting from 3 days from current date usnigf below query
SELECT CURRENT_DATE -3DAY
FROM SYSIBM.SYSDUMMY1
Thanks,
Pradeep |
|
| Back to top |
|
|
Steve Davies
New User
Joined: 15 Oct 2009
Posts: 32
Location: UK
|
|
|
|
Or to phrase your question another way,..... is today the Tuesday before the first Friday of the month?
then you can
| Code: |
select dayofweek(current date)
,dayofmonth(current date + 3 days)
into :ws-dow, :ws-dom
from sysibm.sysdummy1; |
IF WS-DOW = 2 (Tuesday) AND WS-DOM < 8 then it must be the Tuesday before the first Friday of the month |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
Hi rmd3003,
| Quote: |
| Is there a way to check if current date equal first Friday of the month minus three days? |
Below query will give you Output 1 if current date equal first Friday of the month minus three days else it will give 0
| Code: |
SELECT
CASE WHEN (DAYOFWEEK(CURRENT_DATE + 3 DAYS) = 6 AND
DAY(CURRENT_DATE + 3 DAYS) <= 7) THEN 1 ELSE 0 END AS VAL
FROM SYSIBM.SYSDUMMY1;
|
If run on 9/29, output will be 1
Below is sample for testing...
| Code: |
SELECT
CASE WHEN (DAYOFWEEK(DATE('09/29/2009') + 3 DAYS) = 6 AND
DAY(DATE('09/29/2009') + 3 DAYS) <= 7) THEN 1 ELSE 0 END AS VAL
FROM SYSIBM.SYSDUMMY1;
|
similarly If run on 11/03 output will be 0 |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
dt + mod( 7 - dayofweek_iso(dt - 0 days) ,7) days as first_sunday
, dt + mod( 7 - dayofweek_iso(dt - 1 days) ,7) days as first_monday
, dt + mod( 7 - dayofweek_iso(dt - 2 days) ,7) days as first_tuesday
, dt + mod( 7 - dayofweek_iso(dt - 3 days) ,7) days as first_wednesday
, dt + mod( 7 - dayofweek_iso(dt - 4 days) ,7) days as first_thursday
, dt + mod( 7 - dayofweek_iso(dt - 5 days) ,7) days as first_friday
, dt + mod( 7 - dayofweek_iso(dt - 6 days) ,7) days as first_saturday |
will give you for any given date : dt the first monday,.. following this date (or equal if dt is a monday)
thus by filling in the first of the month , you can search for the next friday - 3 days.
| Code: |
select date('2009-10-01') + mod( 7 - dayofweek_iso(date('2009-10-01') - 5 days) ,7) days - 3 days
from sysibm.sysdummy1 |
returns '2009-09-29' |
|
| Back to top |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
Hi RMD,
Why do want DB to do these calculations. In general there will a subroutine / or an accounting table to do such kind of calculations. Check with your project ppl. |
|
| Back to top |
|
|
|
|
