Please change you username into something readable:
This gives the first and last day of the previous quarter:
select to_char(trunc(sysdate - interval '3' month,'Q'),'MMDDYYYY') as first_day, to_char(trunc(sysdate,'Q') - interval '1' day,'MMDDYYYY') as last_day from dual FIRST_DA LAST_DAY -------- -------- 10012018 12312018
Here's one way:
alter session set nls_date_format = 'yyyy-mm-dd'; Session altered. with x (id, dt) as ( select level, add_months(trunc(sysdate), -level) from dual connect by level <48 ) select to_char(dt, 'MMDDYYYY') "MMDDYYYY", -- result you want /* these columns for debug, just showing the dates used */ to_char(dt, 'q') qtr, last_day(add_months(trunc(sysdate, 'q'), -4)) begin_last_qtr, trunc(sysdate, 'q') -1 end_last_qtr from x where dt > last_day(add_months(trunc(sysdate, 'q'), -4)) and dt <= trunc(sysdate, 'q') -1; MMDDYYYY Q BEGIN_LAST END_LAST_Q -------- - ---------- ---------- 12072018 4 2018-09-30 2018-12-31 11072018 4 2018-09-30 2018-12-31 10072018 4 2018-09-30 2018-12-31 3 rows selected.
Note lines 01 to 07 used just to generate dummy data and show the dates used in the where clause.
Lines 09 - 12. also just used to illustrate what is going on.
Line 14 and 15 is the where clause you would need to get the previous qtr results.
As you supplied zero data to work with then that was all necessary.
You just need to use the where clause with your table/s, and your data...
and possibly the select to_char(dt, 'MMDDYYYY') to generate your ambiguous date format display.
Note this forum is for questions related to the SQLDeveloper GUI tool. For generic SQL/PLSQL questions, please post here in the future. Here is another link to their fine posting guidelines on how to best explain your issue and help others help you.
Another possibility is as follows
WITH cte AS ( SELECT trunc(SYSDATE -LEVEL) dt FROM dual CONNECT BY LEVEL <= 365 ) SELECT dt, trunc(add_months(dt,-3),'Q') qs, trunc(dt,'Q')-1 qe FROM cte order by dt ;
add_months(trunc(SYSDATE, 'YYYY'),(to_number(TO_CHAR(SYSDATE, 'Q')- 1)- 1)* 3) quarter_start
, trunc(SYSDATE, 'Q')- 1 quarter_end
Thank you very much for your reply, really appreciate your help.
I changed my display name and it works out.
I am supposed to write my query as below mentioned, so could you please help me where do you want me to use your logic??
where staus = 'Present'
I am sorry this is my first post and I will follow your links.
Thank you for your reply.
Assuming "examdate" is your date field, something akin to the following (or you may adjust the where clause to any of the above suggestions if you prefer).
Select SubName, rollnum, class, section, examdate, results, status, from school.xclass where staus = 'Present' and examdate >= trunc(add_months(sysdate,-3),'Q') and examdate < trunc(sysdate,'Q');
I am sorry to ask this question I am new to SQL and may I know what is the dt ??
- and examdate >= trunc(add_months(dt,-3),'Q')
- and examdate < trunc(dt,'Q');
can I use the same logic in my Oracle SQL ??
Thanks in advance.
Sorry Bale, my bad, "dt" represents the current date aka "sysdate" in Oracle SQL - that was a case of hasty cut and paste, i edited the previous post for correctness.
Also note that this assumes "examdate" is of proper DATE type, *not* some string representation of a date.