INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle Date function - how to find quarter for date field

Oracle Date function - how to find quarter for date field

(OP)
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.

RE: Oracle Date function - how to find quarter for date field

I would start with something like:

CODE

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.... smile

Have fun.

---- Andy

RE: Oracle Date function - how to find quarter for date field

Or, a little better:

CODE

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

Getting smarter... smile

Have fun.

---- Andy

RE: Oracle Date function - how to find quarter for date field

This SQL:

CODE

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:

MY_QTR	HOW_MANY_PER_QTR
1	6297
2	5333
3	3280
4	3291
 

Have fun.

---- Andy

RE: Oracle Date function - how to find quarter for date field

(OP)
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.

RE: Oracle Date function - how to find quarter for date field

(OP)
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');

RE: Oracle Date function - how to find quarter for date field

Niebs2,

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close