Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Tricky query using dates - is there a way to do it???

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rmd3003

New User


Joined: 03 Jul 2006
Posts: 53

PostPosted: Wed Oct 28, 2009 5:44 am    Post subject: Tricky query using dates - is there a way to do it???
Reply with quote

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
View user's profile Send private message

pradeep_123

New User


Joined: 21 Nov 2007
Posts: 9
Location: bhubaneswar

PostPosted: Wed Oct 28, 2009 11:20 am    Post subject: Reply to: Tricky query using dates - is there a way to do it
Reply with quote

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
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Wed Oct 28, 2009 1:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Oct 28, 2009 2:31 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Oct 28, 2009 4:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Wed Oct 28, 2009 5:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us