| View previous topic :: View next topic |
| Author |
Message |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
 Posted: Mon Jan 03, 2005 11:17 am Post subject: SQL Challenge - II |
 |
|
1.Find the 3rd saturday of any given month. The input to you is a date in CCYY-MM-DD format. You need to find the 3rd saturday of that month.
For ex: take current date 2005-01-03 , now the third saturday of this month is 2005-01-15.
2. Find the difference between any 2 dates in terms of months.
Date 1 : 2004-05-01 (CCYY-MM-DD)
Date 2 : 2005-01-03 (CCYY-MM-DD)
The difference between these 2 dates is 8 months.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Jan 05, 2005 11:22 am Post subject: |
|
|
Well No one has attempted to answer this. so let me go ahead and post the solutions. The clue to the first question was posted in this topic
http://www.mvsforums.com/helpboards/viewtopic.php?t=3461
1.Find the 3rd saturday of any given month. The input to you is a date in CCYY-MM-DD format. You need to find the 3rd saturday of that month.
Here is the actual solution.
| Code: |
SELECT A.SAT3 FROM (
SELECT DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT'))
SAT1
,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
7 DAYS ) SAT2
,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
14 DAYS ) SAT3
,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
21 DAYS ) SAT4
,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
28 DAYS ) SAT5
FROM SYSIBM.SYSDUMMY1) A
|
Check this link for a detailed explanation of Last_Day scalar function
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2.40?SHELF=&DT=20010718164132&CASE=
Check this link for a detailed explanation of NEXT_Day scalar function
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2.56?SHELF=&DT=20010718164132&CASE=
2. Find the difference between any 2 dates in terms of months.
Date 1 : 2004-05-01 (CCYY-MM-DD)
Date 2 : 2005-01-03 (CCYY-MM-DD)
Solution:
| Code: |
SELECT INT(SUBSTR(DIGITS(
DATE('2005-01-03') - DATE('2004-05-01')),1,4)) * 12 +
INT(SUBSTR(DIGITS(
DATE('2005-01-03') - DATE('2004-05-01')),5,2))
FROM SYSIBM.SYSDUMMY1;
|
The difference between any 2 dates is given decimal number which specifies the number of years, months, and days between the two dates.
We convert the number into character form using DIGITS function. By doing so the the difference between the dates is given as Character form of length 8(4 bytes for the year , 2 bytes for the months and 2 bytes for the days)
So we multiply the year portion with 12 ( 1 year= 12 months) and add it to the bytes at 5 which holds the months.
Thanks
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mummanen
Beginner
Joined: 02 Feb 2004
Posts: 4
Topics: 1
Location: Sacramento/USA
|
| Posted: Tue Aug 23, 2005 4:09 pm Post subject: |
|
|
The below SQL will work for differance between two dates:
select round(months_between(to_date('2005-01-03', 'YYYY-MM-DD'),to_date('2004-05-01', 'YYYY-MM-DD')))
from dual; |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Tue Aug 23, 2005 4:56 pm Post subject: |
|
|
mummanen,
I beleive that your query works only with Oracle . Db2 does not have the function Months_between
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mummanen
Beginner
Joined: 02 Feb 2004
Posts: 4
Topics: 1
Location: Sacramento/USA
|
| Posted: Wed Aug 24, 2005 12:09 pm Post subject: |
|
|
| I didn't realize that you are talking about only DB2 SQL's. |
|
| Back to top |
|
|
imone
Beginner
Joined: 21 Sep 2005
Posts: 1
Topics: 0
|
| Posted: Wed Sep 21, 2005 4:46 pm Post subject: |
|
|
This can be used to get Third saturday of any month.
| Code: |
SELECT (DATE('2005-02-28') - DAY('2005-02-28') DAY + 1 DAY) +
((7 - DAYOFWEEK(DATE('2005-02-28') - DAY('2005-02-28') DAY + 1 DAY))+
14) DAY AS "THIRD SATURDAY"
FROM SYSIBM.SYSDUMMY1;
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
Pknair
Beginner
Joined: 29 Nov 2005
Posts: 26
Topics: 9
|
| Posted: Fri Apr 28, 2006 10:05 am Post subject: |
|
|
Ths also gives you the 3rd saturday
| Code: |
SELECT 14+8-DAYOFWEEK(DATE(SUBSTR('2006-01-01',6,2)||'/01/'||
SUBSTR('2006-01-01',1,4)))
FROM SYSIBM.SYSDUMMY1
|
pknair |
|
| Back to top |
|
|
Pknair
Beginner
Joined: 29 Nov 2005
Posts: 26
Topics: 9
|
| Posted: Fri Apr 28, 2006 10:11 am Post subject: |
|
|
I am sorry it loks similar to Imone's posting
pknair |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
