|
|
Re: Finding Dates of a Given Month in Single Query...? [message #356785 is a reply to message #356709] |
Sun, 02 November 2008 06:50 |
atric
Messages: 3 Registered: November 2008
|
Junior Member |
|
|
Hi,
Try the below query
This query shows the result for the current month. You can replace Sysdate accordingly to get the result for any given month
SELECT To_Char(Trunc(Sysdate,'MM') + level - 1)||' '||To_Char(Trunc(Sysdate,'MM') + level -1,'DAY') Day
FROM dual
CONNECT BY level <=Last_Day(Trunc(Sysdate))-Trunc(Sysdate,'MM')+1
|
|
|
|
Re: Finding Dates of a Given Month in Single Query...? [message #356823 is a reply to message #356708] |
Sun, 02 November 2008 11:37 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Yes, this is an exercise in two different techniques:
1) date manipulation
2) row generation
Please consider the following code snippets, and read the link provided for more information.
SQL> select sysdate,trunc(sysdate),trunc(sysdate,'mm'),last_day(sysdate),trunc(last_day(sysdate)) from dual
2 /
SYSDATE TRUNC(SYSDATE) TRUNC(SYSDATE,'MM') LAST_DAY(SYSDATE) TRUNC(LAST_DAY(SYSDA
-------------------- -------------------- -------------------- -------------------- --------------------
02-nov-2008 12:34:05 02-nov-2008 00:00:00 01-nov-2008 00:00:00 30-nov-2008 12:34:05 30-nov-2008 00:00:00
SQL> select trunc(last_day(sysdate))-trunc(sysdate,'mm') from dual;
TRUNC(LAST_DAY(SYSDATE))-TRUNC(SYSDATE,'MM')
--------------------------------------------
29
SQL> select trunc(last_day(sysdate))-trunc(sysdate,'mm')+1 from dual;
TRUNC(LAST_DAY(SYSDATE))-TRUNC(SYSDATE,'MM')+1
----------------------------------------------
30
SQL>
Oracle Row Generator Techniques
Good luck, Kevin
|
|
|