| View previous topic :: View next topic |
| Author |
Message |
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
 Posted: Mon Nov 07, 2005 4:04 pm Post subject: Find no: leap years/christmas days between 2 Dates |
 |
|
Hai All,
1.I am interested in a query wherin i can retreive the information as to how many times(number) of leap years occured during a particular period say 1976-05-04 till 2005-11-08.
2.I am interested in a query wherin i can retreive the information as to how many times the festival of Christmas has occured duing a particular period say 1976-05-04 till 2005-11-08.
_________________
Shekar
Grow Technically |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12395
Topics: 75
Location: San Jose
|
| Posted: Mon Nov 07, 2005 6:14 pm Post subject: |
|
|
Shekhar123,
To find the no: of christmas between a range of dates is easy. Since christmas comes every year, it is just the difference between the two dates in terms of years.The result of subtracting one date (DATE2) from another (DATE1) is a date duration that specifies the number of years, months, and days between the two dates. The data type of the result is DECIMAL(8,0). However you need to consider the current year if the date falls before christmas, it cannot be counted into the total. Similary the beginning date.
Here is an untested sql which I think will give you the no: of christmas days between 2 dates.
| Code: |
SELECT INT(SUBSTR(DIGITS(
DATE(CURRENT DATE) - DATE('1976-05-04')),1,4)) -
INT(CASE WHEN MONTH(CURRENT DATE) = 12 AND
DAY(CURRENT DATE) > 24
THEN 0 ELSE 1 END) -
INT(CASE WHEN MONTH(DATE('1976-05-04')) = 12 AND
DAY(DATE('1976-05-04')) > 25
THEN 1 ELSE 0 END)
FROM SYSIBM.SYSDUMMY1;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Nov 07, 2005 11:15 pm Post subject: |
|
|
The query worked perfectly.
The answer is 28. |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Tue Nov 08, 2005 2:10 am Post subject: |
|
|
Kolusu,
| Quote: |
Here is an untested sql
|
Do You really need to test a piece of code  You have an internal "Mega Mainframe" processor  already built inside your head. 
Thanks,
Phantom |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany
|
| Posted: Wed Nov 09, 2005 8:09 am Post subject: |
|
|
1.1: Leap Years:
Problem can be solved with an lookuptable with two columns:
COL1: YEAR, fill table with all possible YEAR
COL2: Indicator, LEAP Year, YES / NO.
It dosn't look like a problem, which can be solved by a single select without the mentioned lookup table. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
