Home » SQL & PL/SQL » SQL & PL/SQL » Finding Dates of a Given Month in Single Query...? (Oracle SQL)
Finding Dates of a Given Month in Single Query...? [message #356708] Sat, 01 November 2008 04:33 Go to next message
lxlranalxl
Messages: 2
Registered: October 2008
Location: PK
Junior Member
i am having problem in writing query that produce all dates + days of a given month...
like ...
01-NOV-08 SATURDAY
02-NOV-08 SUNDAY
03-NOV-08 MONDAY
.
.
.
.
30-NOV-08 SUNDAY

HELP ME DOING THIS
Re: Finding Dates of a Given Month in Single Query...? [message #356709 is a reply to message #356708] Sat, 01 November 2008 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
HELP ME DOING THIS

Just search for "calendar" and/or "row generator" you will find many answers. For instance, in "General" forum there are 2 "puzzle" topics on the subject.

Regards
Michel
Re: Finding Dates of a Given Month in Single Query...? [message #356785 is a reply to message #356709] Sun, 02 November 2008 06:50 Go to previous messageGo to next message
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 #356816 is a reply to message #356785] Sun, 02 November 2008 11:11 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Welcome to the OraFAQ Forum, Atric!

Please, read the OraFAQ Forum Guide; it will help us better understand each others.

It was nice of you to provide complete solution for Lxlranalxl; however, as this question was an obvious training or a homework problem which is asked quite often, it is better NOT to do what you've done (i.e. spoonfeeding /forum/fa/3943/0/). Michel did the right thing - pointed Lxlranalxl into the right direction, provided keywords and it was Lxlranalxl's task to research what they mean and how they can help solving his problem.

Now he had learnt much less than he had a chance to do it himself. So - next time think twice whether the question requires such a reply or not.

Also, reading The Guide will teach you how to properly format the code and preserve formatting (using the [code] tags).
Re: Finding Dates of a Given Month in Single Query...? [message #356823 is a reply to message #356708] Sun, 02 November 2008 11:37 Go to previous message
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
Previous Topic: Help with TRIGGERS
Next Topic: Diff Between Schema and user
Goto Forum:
  


Current Time: Mon Nov 04 05:43:39 CST 2024