Right now am using the below query to extract the date.
FROM c
WHERE date <=TO_DATE ('30-SEP-18', 'DD-MON-YY').
Can you suggest me a way where I dont need to hardcode the date like 30-sep-18.
Note: the example date is 30 sep 2018 because I'm trying to retrieve data for every quarter in an year. So suggest a way to retrieve date for every quarter in an year.
You can use the "Q" format mask to truncate any date down to the quarter, eg
SQL> select sysdate from dual;
SYSDATE
---------
03-OCT-18
1 row selected.
SQL> select trunc(sysdate,'Q') from dual;
TRUNC(SYS
---------
01-OCT-18
1 row selected.
SQL> select round(sysdate,'Q') from dual;
ROUND(SYS
---------
01-OCT-18
1 row selected.
SQL>
SQL> select last_day(trunc(sysdate,'Q')) from dual;
LAST_DAY(
---------
31-OCT-18
1 row selected.
SQL> select last_day(add_months(trunc(sysdate,'Q'),-1)) from dual;
LAST_DAY(
---------
30-SEP-18
1 row selected.
SQL>
SQL> select sysdate+70 from dual;
SYSDATE+7
---------
12-DEC-18
1 row selected.
SQL> select trunc(sysdate+70,'Q') from dual;
TRUNC(SYS
---------
01-OCT-18
1 row selected.
SQL> select round(sysdate+70,'Q') from dual;
ROUND(SYS
---------
01-JAN-19
1 row selected.
SQL>
SQL>
SQL>