Oracle Date function - how to find quarter for date field

[niebs2]

Hello,
I am tring to write sql to find the quarter for a date field and would like the results by quarter along with member count.
So i have a date field from a table. I am getting messed up using the extract function to get the month from the date field and then finding the quarter it belongs too. Any help would be appreciated.

Select extract((month from aDate) +2/3 'MM'), count(member_key) from tablename ; the extract part is supposed to give me the quarter from the date field but its not correct. I havent found anything good on the web to help me get it correct.

Thanks.

 

[Andrzejek]

I would start with something like:

SELECT 
CASE TO_CHAR(SYSDATE, 'MM')
  WHEN '01' THEN 'First Qtr'
  WHEN '02' THEN 'First Qtr'
  WHEN '03' THEN 'First Qtr'
  WHEN '04' THEN 'Second Qtr'
  WHEN '05' THEN 'Second Qtr'
END AS MY_QTR
FROM DUAL

But I am sure some smarter people than me will come up with something better....

Have fun.

---- Andy

 

[Andrzejek]

Or, a little better:

SELECT TO_CHAR(TO_DATE('12/26/2012', 'MM/DD/YYYY'), [red]'Q'[/red]) AS MY_QTR
FROM DUAL

Getting smarter...

Have fun.

---- Andy

 

[Andrzejek]

This SQL:

SELECT TO_CHAR(date_field, 'Q') AS MY_QTR, 
    COUNT(date_field) AS HOW_MANY_PER_QTR
FROM tablename 
GROUP BY TO_CHAR(date_field, 'Q')
ORDER BY 1

gave me something like this:

[pre]
MY_QTR HOW_MANY_PER_QTR
1 6297
2 5333
3 3280
4 3291
[/pre]

Have fun.

---- Andy

 

[niebs2]

Thank you for your help. The last one did the trick. If i wanted to add a where clause between 2 ranges would i have to make the date field a different variable instead of to_date?
Also i added instead of 'Q' i wanted it using 'Q-YYYY' to get quarter year if range is longer then a year.

 

[niebs2]

i think this might help you or get you close to what you want. just change the date field range to what you need with between.
Select to_char(datefield, 'YYYY-Q') AS my_qtr, count(members)
FROM table_name
WHERE datefield >= to_date('01/01/2011', 'MM/DD'YYYY')
group by to_char(datefield, 'YYYY-Q')
order by to_char(datefield, 'YYYY-Q');

 

[SantaMufasa]

Niebs2,

If you appreciate Andy's help, then please click [Like this post? Star it !] on his helpful post.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.