Home » SQL & PL/SQL » SQL & PL/SQL » Month Extracting
Month Extracting [message #315827] Tue, 22 April 2008 22:06 Go to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi All,

Current output is...
05-APR-08
19-APR-08
03-MAY-08
17-MAY-08
31-MAY-08
14-JUN-08
28-JUN-08
12-JUL-08
26-JUL-08

I want to show only the list of days for current month days like...
05-APR-08
19-APR-08

How to do? Thanks in advance.

-Karthik
Re: Month Extracting [message #315828 is a reply to message #315827] Tue, 22 April 2008 22:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at the LAST_DAY() and ADD_MONTHS() functions. You can use LAST_DAY() on its own to get the end of month. Then use ADD_MONTHS() to subtract a month, and +1 to add one day - that gets you the beginning of the month.

I leave the syntax to you as an exercise.

Ross Leishman
Re: Month Extracting [message #315829 is a reply to message #315827] Tue, 22 April 2008 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>How to do?
by using SQL
Re: Month Extracting [message #315830 is a reply to message #315827] Tue, 22 April 2008 22:32 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I got the solution,

I added where condition, by checking the output values with the sysdate using trunc function as...

where trunc(sysdate,'MM') = output values,

this will fetch the list of dates for the current month.
Re: Month Extracting [message #315837 is a reply to message #315827] Tue, 22 April 2008 23:24 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
If your search criteria is for current year,with month

SELECT * FROM emp WHERE hiredate BETWEEN TRUNC(sysdate,'mm') AND LAST_DAY(sysdate);

If you want to search only on the basis of Month <For any Year >

SELECT * FROM emp WHERE TO_CHAR(hiredate,'fmmonth')=LOWER('&month');

SELECT * FROM emp WHERE EXTRACT(MONTH FROM hiredate) = 4;

Regards
Deepak
Re: Month Extracting [message #315839 is a reply to message #315837] Tue, 22 April 2008 23:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@kecd_deepak,

Please note the following from OraFAQ Forum Guide:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.

Also read "How to format your post?" section.

Regards
Michel

[Updated on: Tue, 22 April 2008 23:27]

Report message to a moderator

Re: Month Extracting [message #315843 is a reply to message #315827] Tue, 22 April 2008 23:33 Go to previous message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hi Michel,
Thanks for remind me the Rules,
Sorry for that post.
I follow the rules .......

Regards
deepak
Previous Topic: measure the execution time for a query in PL/SQL
Next Topic: Bind variable not declared???
Goto Forum:
  


Current Time: Tue Dec 06 02:18:26 CST 2016

Total time taken to generate the page: 0.15553 seconds