Using SELECT statements

190 pts.
Tags:
Oracle
Oracle development
SELECT statement
Can we get the number of saturdays in any month using a SELECT statement(i.e without creating a table or function)? If so, can you give me the logic?
ASKED: February 27, 2008  9:48 PM
UPDATED: February 28, 2008  5:06 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can use a combination of oracle functions within one select statement to get your answer. The following code seems to work:

select (next_day(last_day(sysdate),’SAT’) – next_day(last_day(add_months(sysdate,-1)),’SAT’)) / 7 from dual;

Sysdate gives the current date; but you could replace sysdate with any date

The concept is to get the number of days between the first saturday of the month and the first saturday of the next month. Then divide by 7.

The first Saturday of NEXT month can be found by getting the last day of the current month <last_day(sysdate),> and then getting the next Saturday <next_day(last_day(sysdate),’SAT’)>

To get the first Saturday of the CURRENT month, we first need to get the date one month back <add_months(sysdate,-1)>. Then we can get the last day of that month <last_day(add_months(sysdate,-1))>. Finally, we can get the first Saturday of the current month (which is the next Saturday after the last day of the previous month) <next_day(last_day(add_months(sysdate,-1)),’SAT’)) >

Subtract those two dates and divide by 7 and you have your answer.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following