Home » SQL & PL/SQL » SQL & PL/SQL » a small query in oracle
a small query in oracle [message #6748] Sun, 04 May 2003 23:56 Go to next message
robin baby
Messages: 13
Registered: May 2003
Junior Member
Can i disply all days in a month with date using a single query?

Report message to a moderator

Re: a small query in oracle [message #6750 is a reply to message #6748] Mon, 05 May 2003 01:00 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
 
SELECT yourdate+r-1 new_date
  FROM ( SELECT rownum r
              , to_date('&mon'||'&year','MONYYYY') yourdate
           FROM all_objects -- any table with at least 31 days 'll do.
          WHERE rownum < 32 -- a month can have 31 days max.
       )
 WHERE r <= to_number(to_char(last_day(yourdate),'DD'))
With the example above, and I'm sure that if you'll search the board you'll find plenty variants of the same flavour, you enter a month in "abbreviated format" (JAN,FEB,etc.) and a year in four digits. The query returns all dates of this particular month.

I just searched the boards, and Barbara Boehmer has a similar query in one of her answers. It pays of to use the search functionality of the boards from time to time.

HTH,
MHE

Report message to a moderator

Previous Topic: Oracle 9i HELP!
Next Topic: Retrive earlier 13 Months.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 23:32:55 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.