Requires Free Membership to View
I'll start with a solution and finish with an explanation.
select ename,
hiredate,
next_day(
last_day(
add_months(
hiredate,
case when extract (day from hiredate) > 15
then 1
else 0
end
)
)-7,
'FRIDAY'
) as paydate
from emp
The CASE statement gives us either a 1 if the pay date should be next month or a zero if it should be this month. The resulting number of months is added to the HIREDATE using the ADD_MONTHS function. LAST_DAY gives us the last day of the month, from which we subtract 7 (number of days in a week) to give us a starting point for the NEXT_DAY function. The NEXT_DAY function finds the next occurrence of a given day after a given date. In this case, we are finding the next Friday. This nesting of functions always results in the last Friday of the month.
Here are the resulting rows:
ENAME HIREDATE PAYDATE ---------- ----------- ----------- SMITH 12/17/1980 1/30/1981 ALLEN 2/20/1981 3/27/1981 WARD 2/22/1981 3/27/1981 JONES 4/2/1981 4/24/1981 MARTIN 9/28/1981 10/30/1981 BLAKE 5/1/1981 5/29/1981 CLARK 6/9/1981 6/26/1981 SCOTT 4/19/1987 5/29/1987 KING 11/17/1981 12/25/1981 TURNER 9/8/1981 9/25/1981 ADAMS 5/23/1987 6/26/1987 JAMES 12/3/1981 12/25/1981 FORD 12/3/1981 12/25/1981 MILLER 1/23/1982 2/26/1982
For More Information
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation