OraFAQ Forum: SQL & PL/SQL » Date Functions - Finding the quarter of the year

Home » SQL & PL/SQL » SQL & PL/SQL » Date Functions - Finding the quarter of the year

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date Functions - Finding the quarter of the year [message #166362]

Wed, 05 April 2006 23:01

CW2005
Messages: 29
Registered: February 2005

Junior Member

Hi All

I'm building a report that requires me to find how many records were created in the 'last quarter'.

In example

Months Jan, Feb, Mar are in Quarter 1.
Months Apr, May, Jun are in Quarter 2.
Months Jul, Aug, Sep are in Quarter 3.
Months Oct, Nov, Dec are in Quarter 4.

If I ran the report today, as we were in April, the current quarter would be considered as Quarter 2. The report would therefore have to report on all records created in Quarter 1.

I dont think there is any date function that will allow me to do this, can anyone help me out?

Thanks in advance.

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #166367 is a reply to message #166362]

Thu, 06 April 2006 00:21

RAS_SANKAR
Messages: 42
Registered: March 2006
Location: India

Member

hi

u try out this code, it may be useful to u.

first u write the following query in query object
select * from table where date between :d1 and :d2
and write the following code in
before-parameter-form trigger

 if to_char(sysdate,'mm') in (1,2,3) then
  	:d1 := to_date('01-jan-'||to_char(sysdate,'yyyy'));
  	:d2 := to_date('31-mar-'||to_char(sysdate,'yyyy'));
  elsif to_char(sysdate,'mm') in (4,5,6) then
  	:d1 := to_date('01-apr-'||to_char(sysdate,'yyyy'));
  	:d2 := to_date('30-jun-'||to_char(sysdate,'yyyy'));
	elsif to_char(sysdate,'mm') in (7,8,9) then
  	:d1 := to_date('01-jul-'||to_char(sysdate,'yyyy'));
  	:d2 := to_date('30-sep-'||to_char(sysdate,'yyyy'));
  elsif to_char(sysdate,'mm') in (10,11,12) then
  	:d1 := to_date('01-oct-'||to_char(sysdate,'yyyy'));
  	:d2 := to_date('31-dec-'||to_char(sysdate,'yyyy'));  	                                                     	                                                   
  end if;

-Ras

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #166369 is a reply to message #166367]

Thu, 06 April 2006 00:23

CW2005
Messages: 29
Registered: February 2005

Junior Member

Hi Thanks

I should have explained.

I need to do this in an SQL query - not PL/SQL.

Thanks

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #166374 is a reply to message #166369]

Thu, 06 April 2006 01:10

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Let's get the good ol' manual out, shall we? In the SQL Reference, Chapter 2: Basic Elements of Oracle SQL, in Table 2-15 (Datetime Format Elements) we find: "Q: Quarter of year (1, 2, 3, 4; JAN-MAR = 1)"

So, we can do it directly:

SQL> SELECT TO_CHAR(d,'Month') themonth
  2       , TO_CHAR(d, 'Q') quarter
  3  FROM   ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'Y'), LEVEL-1) d
  4           FROM   dual
  5           CONNECT BY LEVEL <= 12
  6         )
  7  /

THEMONTH  Q
--------- -
January   1
February  1
March     1
April     2
May       2
June      2
July      3
August    3
September 3
October   4
November  4
December  4

12 rows selected.

MHE

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #166379 is a reply to message #166374]

Thu, 06 April 2006 01:19

CW2005
Messages: 29
Registered: February 2005

Junior Member

Hi Maaher

Thank you so much for that.
Would you be able to show me how I can find out what the last quarter was, based on the current quarter?

Thank you so much in advance.

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #166382 is a reply to message #166379]

Thu, 06 April 2006 01:29

CW2005
Messages: 29
Registered: February 2005

Junior Member

Hi Again Maaher

I think I've got it...

I could do this....

select to_char(add_months(sysdate,-3),'Q'), add_months(sysdate,-3) from dual

Thank you Smile

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #649305 is a reply to message #166362]

Tue, 22 March 2016 03:04

RaghaSudeep
Messages: 1
Registered: March 2016

Junior Member

Just try this

declare
  d_date date:=to_date('&d_date','dd/mm/yyyy');
begin
  dbms_output.put_line('quarter no' ||to_char(d_date,'q'));
end;
/

[mod-edit: code tags and indentation added and image inserted into message body by bb;
if an old thread is resurrected, might as well make the response pretty]

Attachment: date quarter.PNG
(Size: 6.59KB, Downloaded 1949 times)

[Updated on: Tue, 22 March 2016 18:10] by Moderator

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #649307 is a reply to message #649305]

Tue, 22 March 2016 03:38

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your purpose answering to a 10 years old question which has already been answered?

Report message to a moderator

Re: Date Functions - Finding the quarter of the year [message #649335 is a reply to message #649307]

Tue, 22 March 2016 16:56

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Over to the forum ancient thread historian.
So, professor Cadot, thankyou for taking the time to talk to ORAnews. Tell us, is this the oldest thread ever to have been brought back to life? What can science learn from these acts of thread necromancy?

Report message to a moderator