select hiredate, to_char(hiredate,'q') from emp
Where to_char(hiredate,'q')=to_char(decode(to_number(to_char(sysdate,'q'))-1,0,4,to_number(to_char(sysdate,'q'))-1));
This works but it returns data for all of the years. I need to retrieve data for the current year or previous year if the current quarter is quarter 1. Any ideas???
Have just modified the above query a bit:
----------------------------------------------------------
select hiredate, to_char(hiredate,'q') from emp
Where to_char(hiredate,'q-yyyy') =
decode(to_number(to_char(sysdate,'q'))-1,0,'4-'||to_char(to_number(to_char(sysdate,'yyyy'))-1),
to_char(to_number(to_char(sysdate,'q'))-1)||'-'||to_char(sysdate,'yyyy'))
---------------------------OR-------------------------------
select hiredate, to_char(hiredate,'q') from emp
Where to_char(hiredate,'q') = to_char(decode(to_number(to_char(sysdate,'q'))-1,0,4,to_number(to_char(sysdate,'q'))-1))
and to_char(hiredate,'yyyy') = decode(to_number(to_char(sysdate,'q'))-1,0,to_number(to_char(sysdate,'yyyy'))-1, to_char(sysdate,'yyyy'));
-----------------------------------------------------------
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.