SQL :- Date Function

--====================================================

SQL :- Date Function

--====================================================

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

--SESSIONTIMEZONE   Get the session time zone

--SYSTIMESTAMP   Return the system date and time that includes fractional seconds and time zone.

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

SELECT SESSIONTIMEZONE, SYSTIMESTAMP FROM dual;

Result :-

SESSIONTIMEZONE	SYSTIMESTAMP
+05:30	13-APR-2020 05:10:00.666971 PM +05:30

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

--SYSDATE   Return the current system date and time of the operating system where the Oracle Database resides.

--CURRENT_DATE    Return the current date and time in the session time zone

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

select SYSDATE ,CURRENT_DATE  from DUAL

Result :-

SYSDATE	CURRENT_DATE
13-Apr-2020 05:11:05 PM	13-Apr-2020 05:11:05 PM

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

--CURRENT_TIMESTAMP    Return the current date and time with time zone in the session time zone

--LOCALTIMESTAMP  Return a TIMESTAMP value that represents the current date and time in the session time zone.

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

SELECT CURRENT_TIMESTAMP,LOCALTIMESTAMP FROM dual

Result :-

CURRENT_TIMESTAMP	LOCALTIMESTAMP
13-APR-2020 05:11:43.326796 PM +05:30	13-APR-2020 05:11:43.326796 PM

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

--ADD_MONTHS     Add a number of months (n) to a date and return the same day which is n of months away.

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

select  sysdate, ADD_MONTHS( sysdate, 3 ),ADD_MONTHS( '13-APR-2020', 3 ) from DUAL

Result :-

SYSDATE	ADD_MONTHS(SYSDATE,3)	ADD_MONTHS('13-APR-2020',3)
13-Apr-2020 05:12:32 PM	13-Jul-2020 05:12:32 PM	13-Jul-20

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

--LAST_DAY   Gets the last day of the month of a specified date.

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

select  LAST_DAY(SYSDATE),LAST_DAY('13-JAN-2020') from DUAL

Result :-

LAST_DAY(SYSDATE)	LAST_DAY('13-JAN-2020')
30-Apr-2020 05:13:49 PM	31-Jan-2020

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

--MONTHS_BETWEEN   Return the number of months between two dates.

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

select  MONTHS_BETWEEN( DATE '2017-07-01', DATE '2017-01-01' ), MONTHS_BETWEEN( '13-APR-2020' ,'1-JAN-2020' )  from DUAL

Result :-

MONTHS_BETWEEN(DATE'2017-07-01',DATE'2017-01-01')	MONTHS_BETWEEN('13-APR-2020','1-JAN-2020')
6	3.38709677419355

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

--NEXT_DAY   Get the first weekday that is later than a specified date.

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

select  NEXT_DAY( SYSDATE, 'MONDAY' ) from DUAL

Result :-  20-Apr-2020 05:15:07 PM

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

--EXTRACT    Extract a value of a date time field e.g., YEAR, MONTH, DAY, … from a date time value.

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

select EXTRACT(YEAR FROM SYSDATE) ,EXTRACT(MONTH FROM SYSDATE) ,EXTRACT(DAY FROM SYSDATE) from DUAL

Result :-

EXTRACT(YEARFROMSYSDATE)	EXTRACT(MONTHFROMSYSDATE)	EXTRACT(DAYFROMSYSDATE)
2020	4	13

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

--ROUND and TRUNC

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

select TRUNC(5.3 ), ROUND(5.3) FROM DUAL

Result :-

TRUNC(5.3)	ROUND(5.3)
5	5

select TRUNC( 5.6), ROUND(5.6)   from dual

Result :-

TRUNC(5.6)	ROUND(5.6)
5	6

select SYSDATE,TRUNC(sysdate,'YYYY'), ROUND(SYSDATE,'YYYY'),TRUNC(TO_DATE('1-SEP-2020'),'YYYY'), ROUND(TO_DATE('1-SEP-2020'),'YYYY')

from dual

Result :-

SYSDATE	TRUNC(SYSDATE,'YYYY')	ROUND(SYSDATE,'YYYY')
13-Apr-2020 05:17:20 PM	01-Jan-20	01-Jan-20

TRUNC(TO_DATE('1-SEP-2020'),'YYYY')	ROUND(TO_DATE('1-SEP-2020'),'YYYY')
01-Jan-20	01-Jan-21

select TRUNC(TO_DATE('15-AUG-2020'),'Q'), ROUND(TO_DATE('16-AUG-2020'),'Q')

from dual

1-APR to JUN

1-JUL to Sep

1-Oct to Dec

1-JAN to Mar-21

Result :-

TRUNC(TO_DATE('15-AUG-2020'),'Q')	ROUND(TO_DATE('16-AUG-2020'),'Q')
01-Jul-2020	01-Oct-2020

select sysdate, TRUNC(sysdate,'DD'), ROUND(SYSDATE,'DD')--, TRUNC(TO_DATE('15-AUG-2020'),'DD'), ROUND(TO_DATE('18-AUG-2020'),'DD')

from dual

Result :-

SYSDATE	TRUNC(SYSDATE,'DD')	ROUND(SYSDATE,'DD')
13-Apr-2020 05:20:33 PM	13-Apr-20	14-Apr-20