Home » SQL & PL/SQL » SQL & PL/SQL » START AND END DATE OF MONTH
START AND END DATE OF MONTH [message #437484] Mon, 04 January 2010 13:13 Go to next message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
Hi All,

can anyone please help me out i m stuck badly

In SQL i m trying to display STARTING DATE OF MONTH till END DATE of that month

for EXAMPLE

01/12/2009
02/12/2009
03/12/2009
04/12/2009
05/12/2009
06/12/2009
07/12/2009
08/12/2009
09/12/2009
10/12/2009
11/12/2009
12/12/2009
13/12/2009
14/12/2009
15/12/2009
.
.
.
.
25/12/2009
.
.
28/12/2009
29/12/2009
30/12/2009
31/12/2009
Re: START AND END DATE OF MONTH [message #437485 is a reply to message #437484] Mon, 04 January 2010 13:20 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Technique which is capable of doing that is called a row generator. Search the board for examples, and read this article.
Re: START AND END DATE OF MONTH [message #437489 is a reply to message #437485] Mon, 04 January 2010 13:42 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The trim and add_months functions also might be of use to you.
Re: START AND END DATE OF MONTH [message #437491 is a reply to message #437484] Mon, 04 January 2010 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also search for "calendar" this will give you topics applying row generator to generate days, weeks, months and so on.

Regards
Michel
Re: START AND END DATE OF MONTH [message #437707 is a reply to message #437484] Tue, 05 January 2010 13:29 Go to previous messageGo to next message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
thanx for your reply

before posting i searched the forum,but nothing worked out
i even tried this


select trunc(TO_DATE('01/12/2009','DD/MM/YYYY')), last_day(to_date('31/12/2009', 'DD/MM/YYYY')) from dual;
Re: START AND END DATE OF MONTH [message #437709 is a reply to message #437707] Tue, 05 January 2010 13:48 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Did you follow the link given in previous post?

You could have find the answer in there itself. Please go through the link.It will be helpful for you to understand.
A solution given by others never going to help you.You can never learn.


SQL> select add_months(last_day(sysdate),-1)+1 + rownum -1  
			from dual connect by level <= last_day(sysdate)- add_months(last_day(sysdate),-1);

ADD_MONTH                                                                                                                    
---------                                                                                                                    
01-JAN-10                                                                                                                    
02-JAN-10                                                                                                                    
03-JAN-10                                                                                                                    
04-JAN-10                                                                                                                    
05-JAN-10                                                                                                                    
06-JAN-10                                                                                                                    
07-JAN-10                                                                                                                    
08-JAN-10                                                                                                                    
09-JAN-10                                                                                                                    
10-JAN-10                                                                                                                    
11-JAN-10                                                                                                                    
12-JAN-10                                                                                                                    
13-JAN-10                                                                                                                    
14-JAN-10                                                                                                                    
15-JAN-10                                                                                                                    
16-JAN-10                                                                                                                    
17-JAN-10                                                                                                                    
18-JAN-10                                                                                                                    
19-JAN-10                                                                                                                    
20-JAN-10                                                                                                                    
21-JAN-10                                                                                                                    
22-JAN-10                                                                                                                    
23-JAN-10                                                                                                                    
24-JAN-10                                                                                                                    
25-JAN-10                                                                                                                    
26-JAN-10                                                                                                                    
27-JAN-10                                                                                                                    
28-JAN-10                                                                                                                    
29-JAN-10                                                                                                                    
30-JAN-10                                                                                                                    
31-JAN-10                                                                                                                    

31 rows selected.

Elapsed: 00:00:00.18
SQL> spool off




Modify the sql using trunc.


or,


From the link provided by Littlefoot Sir,


SELECT TRUNC(SYSDATE,'Y')+ROWNUM-1 THEDATE
FROM   ( SELECT 1 just_a_column
         FROM dual
         CONNECT BY LEVEL <= 366
       )
WHERE  ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)-TRUNC(SYSDATE,'Y')

Use filter as per your requirement
Append the condition with where clause.

Regards,
Ved

[Updated on: Tue, 05 January 2010 13:56]

Report message to a moderator

Re: START AND END DATE OF MONTH [message #437710 is a reply to message #437709] Tue, 05 January 2010 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Its_me_ved wrote on Tue, 05 January 2010 20:29
...
Well, dont say that I am violating the rules by providing direct answers. Smile Its just an alternative.I provided hint initially.
...

And what is your "excuse" here?

Regards
Michel

[Updated on: Tue, 05 January 2010 13:59]

Report message to a moderator

Re: START AND END DATE OF MONTH [message #437711 is a reply to message #437710] Tue, 05 January 2010 14:02 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

before posting i searched the forum,but nothing worked out
i even tried this


select trunc(TO_DATE('01/12/2009','DD/MM/YYYY')), last_day(to_date('31/12/2009', 'DD/MM/YYYY')) from dual;


He has provided what he tried so far.

Regards,
Ved

[Updated on: Tue, 05 January 2010 14:04]

Report message to a moderator

Re: START AND END DATE OF MONTH [message #437712 is a reply to message #437711] Tue, 05 January 2010 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And he obviously didn't read the provided link, so next time OP could post "I tried select * from dual and it does not work"?

Regards
Michel
Re: START AND END DATE OF MONTH [message #437714 is a reply to message #437712] Tue, 05 January 2010 14:25 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Good question!!

OP is a newbie here, seeing his a few post (14 posts) I prefer to help him. He provided what he tried not the select * from dual;
Let him not feel that people discourages here or struggle for such a simple answer.

My aplogies if I am wrong..But my intention is to make people visit this forum.Those newbie should not scare thinking that they would be criticized for small small questions.

Regards,
Ved


Re: START AND END DATE OF MONTH [message #437717 is a reply to message #437484] Tue, 05 January 2010 14:36 Go to previous messageGo to next message
mubeenmoin
Messages: 15
Registered: April 2009
Location: KUWAIT
Junior Member
Thanks VED it really worked
thanks a lot

Mr.Michel Cadot its not that i din try, tried lots

thanks once again VED
Re: START AND END DATE OF MONTH [message #437718 is a reply to message #437717] Tue, 05 January 2010 14:38 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tried maybe but read the article no.

Regards
Michel
Previous Topic: Mutating Trigger
Next Topic: Fixed Width output file
Goto Forum:
  


Current Time: Mon Sep 26 02:34:23 CDT 2016

Total time taken to generate the page: 0.11135 seconds