star date and end date of year in oracle

SQL> SELECT round(sysdate,'YYYY'),add_months(trunc(sysdate,'YYYY')-1,12) from dual ;
------


SQL> select TRUNC(TO_DATE(sysdate),'MM'),LAST_DAY(sysdate) from dual;

TRUNC(TO_DATE(S LAST_DAY(SYSDAT
--------------- ---------------

01-FEB-20       29-FEB-20

to_char(trunc(to_date(:P_YR,'rrrr'),'rr'),'dd-mon-rr') and to_char(round(to_date(:P_YR,'rrrr'),'rr')-1,'dd-mon-rr')


to_char(trunc(to_date(:P_YR,'rrrr'),'rr'),'dd-mon-rr') and to_char(round(to_date(:P_YR+1,'rrrr'),'rr')-1,'dd-mon-rr')



ROUND(SYS ADD_MONTH
--------- ---------
01-JAN-17 31-DEC-17

star date and end date of Month in oracle

SQL> SELECT LAST_DAY(sysdate) AS lastday_test
  2  FROM dual;

LASTDAY_T
---------
31-MAR-17



star date and end date of week in oracle


You can subtract a date from a date in Oracle. The result will be in days. You can also multiply by 24 to get hours and so on.

select 24 * (to_date('2014-10-10 22:00', 'YYYY-MM-DD hh24:mi') - to_date('2014-10- 9 21:00', 'YYYY-MM-DD hh24:mi')) difference_in_hours

from dual;

Calculates the number of months between two dates.

select MONTHS_BETWEEN ('31-JAN-2014', '28-FEB-2014')

from dual

select MONTHS_BETWEEN ('31-MAR-2013', '28-FEB-2013')

from dual

Get Month Start and End Dates for a Given Period (i.e. Year)

SQL> select trunc(add_months('01-JUL-10', level-1), 'MM') start_date,
  2  last_day(add_months('01-JUL-10', level-1)) end_date
  3  from dual connect by level <= ceil(months_between('30-JUN-11', '01-JUL-10')) order by 1;

START_DAT END_DATE
--------- ---------
01-JUL-10 31-JUL-10
01-AUG-10 31-AUG-10
01-SEP-10 30-SEP-10
01-OCT-10 31-OCT-10
01-NOV-10 30-NOV-10
01-DEC-10 31-DEC-10
01-JAN-11 31-JAN-11
01-FEB-11 28-FEB-11
01-MAR-11 31-MAR-11
01-APR-11 30-APR-11
01-MAY-11 31-MAY-11

START_DAT END_DATE
--------- ---------
01-JUN-11 30-JUN-11

12 rows selected.

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 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;

--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;


The following example adds 5 months to the hire date of each employee to give an evaluation date:

SELECT last_name, hire_date, TO_CHAR(
   ADD_MONTHS(LAST_DAY(hire_date), 5)) "Eval Date"
   FROM employees;

LAST_NAME                 HIRE_DATE Eval Date
------------------------- --------- ---------
King                      17-JUN-87 30-NOV-87
Kochhar                   21-SEP-89 28-FEB-90
De Haan                   13-JAN-93 30-JUN-93
Hunold                    03-JAN-90 30-JUN-90
Ernst                     21-MAY-91 31-OCT-91
Austin                    25-JUN-97 30-NOV-97
Pataballa                 05-FEB-98 31-JUL-98
Lorentz                   07-FEB-99 31-JUL-99

SQL> SELECT TRIM(TO_DATE('29 Jan 2015'
  2                  ,'DD MON YY')) FROM DUAL;

TRIM(TO_D
---------
29-JAN-15 

SQL> select TO_CHAR(sysdate, 'dd MON yyyy', 'NLS_DATE_LANGUAGE=AMERICAN') from dual;

TO_CHAR(SYS
-----------
12 MAR 2017 

---------------------------

-- select MAX(ADD_MONTHS(trunc(at_dt,'MON')+25,-1)) into :adt from at_proc_mast;
-- select MAX(ADD_MONTHS(trunc(at_dt,'MON')+25,-1)) into :dt1 from at_proc_mast
--  where at_dt <> (select max(add_months(trunc(at_dt,'mon')+25,0)) from at_proc_mast);
 select max(at_dt) into :adt from emp_attn_data;
 select max(at_dt) into :bdt from emp_attn_data;