DATE Functions in SQL / PL/SQL

select to_char(sysdate,'MON YY')FROM DUAL;

SELECT LAST_DAY(SYSDATE) FROM DUAL;

SELECT next_DAY(SYSDATE,'SUN') FROM DUAL;

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('01-02-2011','DD-MM-YYYY'))FROM DUAL;

SELECT to_date('01-JAN-2006')FROM DUAL;

SELECT to_date('01-01-2004','DD-MM-YYYY')FROM DUAL;

SELECT to_DATE('31-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS')FROM DUAL;

SELECT TO_CHAR(to_DATE('31-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS'))FROM DUAL;

SELECT TO_CHAR(TO_DATE('31-12-2006 23:34:59','HH12:MI:SS'))FROM DUAL;

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH12:MI:SS')FROM DUAL;
----first date in current year---
SELECT TRUNC(SYSDATE,'YYYY')FROM DUAL;
----laste date in current year---
select last_day(add_months(trunc(sysdate,'YYYY'), 11)) from dual
--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;
/

Share on Google Plus

About Mindsforest