Actual Usage of Date Functions

========== Extract Time Upto Miliseconds from Systimetsamp or current_timestamp===

select systimestamp ,
to_char(systimestamp,'HH24:MI:SS-FF') Systimestamp_ExtractMilisecond,
CURRENT_TIMESTAMP,
to_char(CURRENT_TIMESTAMP,'HH24:MI:SS-FF') Currtimestamp_ExtractMS from dual


=========financial year dates=============

SELECT TO_DATE ('01-APR-' || TO_CHAR (ADD_MONTHS (TRUNC (to_date(:p_from_date,'DD-MON-RRRR')),-12), 'YYYY'))  FY_START from dual

SELECT TO_DATE ('31-MAR-' || TO_CHAR (ADD_MONTHS (TRUNC (to_date(:p_from_date,'DD-MON-RRRR')),-1), 'YYYY') )  FY_end from dual  





How to get first day and last date of week, month, quarter, year in Oracle
--First day of current week(sunday)
select TRUNC(SYSDATE, 'Day') from dual;
--FIRST DAY OF CURRENT FINANCIAL YEAR
select ('01-APR-' || TO_CHAR (sysdate-1, 'YYYY'))  from dual
--First day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day') from dual;
--First day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day') from dual;
--First day of current month
select TRUNC(SYSDATE , 'Month') from dual;
--First day of previous month
select TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month') from dual;
--First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , 'Month') from dual;
--First day of current year
select TRUNC(SYSDATE , 'Year') from dual;
--First day of previous year
select TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year') from dual;
--First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , 'Year'),12) from dual;
-- First Day of Current quater
select TRUNC(SYSDATE , 'Q') from dual;
--  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),-3) from dual;
--  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3) from dual;


-------first date of previous 3 months

 select ADD_MONTHS(LAST_DAY(TRUNC(TRUNC(to_date(sysdate,'DD-MON-RRRR') , 'Month')-1 , 'Month')),-3)+1 from dual


--Last day of current week(sunday)
select TRUNC(SYSDATE, 'Day')+6 from dual;
--Last day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day')+6 from dual;
--Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day')+6 from dual;
--Last day of current month
select LAST_DAY(TRUNC(SYSDATE , 'Month')) from dual;
--Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month')) from dual;
--Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , 'Month')) from dual;
--Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Year'),11)) from dual;
--Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)) from dual;
--Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),-13)) from dual;
-- Last Day of Current quater
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),2)) from dual;
--  Last Day of Previous Quarter
select TRUNC(SYSDATE , 'Q')-1 from dual;
--  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),5)) from dual;