DATE Functions in Oracle - to Manage Date & Time

Hi ,Today We will discuss about DATE Functions in oracle.

DATE Related Functions:

1.CURRENT_DATE

The Oracle CURRENT_DATE function returns the current date in the session time zone.

syntax:

   CURRENT_DATE

Example

SELECT
  CURRENT_DATE
FROM
  DUAL;

It will return the current date.

2.EXTRACT:

The Oracle EXTRACT() function extracts a specific component (year, month, day, hour, minute, second, etc.,) from a datetime or an interval value.

syntax:

EXTRACT(field FROM source)

Where,

Field is

> Year , Month , Day

> Hour , Minutes, Second

> Timezone hour , minute , region ,addr

Source is

> Date

> Interval

Example:

SELECT  EXTRACT( MONTHFROMTO_DATE( '31-Dec-1999 15:30:20 ',  'DD-Mon-YYYY HH24:MI:SS' ) ) MONTH
FROM   DUAL;

3. ADD_MONTHS:

Oracle ADD_MONTHS() function adds a number of month (n) to a DATE and returns the same day n of months away.

Syntax:

ADD_MONTHS(date_expression, month)

Example:

SELECT  
 ADD_MONTHS( DATE'2021-06-15', 1 ) 
 FROM   dual;

Output:

15-JUN-2021

4.LAST_DAY

The Oracle LAST_DAY() takes a DATE argument and returns the last day of the month of that date.

syntax:

The following illustrates the syntax of the Oracle LAST_DAY() function:


LAST_DAY(date)

Example:

SELECT
LAST_DAY(SYSDATE) 
FROM   dual;

5.NEXT_DAY

The Oracle NEXT_DAY() function returns the date of the first weekday specified by day name that is later than a date.

syntax:

The following illustrates the syntax of the Oracle LAST_DAY() function:


NEXT_DAY(date,weekday)

Example:

SELECT  
 NEXT_DAY( DATE'2021-06-16', 'SUNDAY' ) 
 FROM   dual;

Output:

20-JUN-2021

6.MONTHS_BETWEEN:

The Oracle MONTHS_BETWEEN() function returns the number of months between two dates.

syntax:

The following illustrates the syntax of the Oracle MONTHS_BETWEEN() function:


MONTHS_BETWEEN(minuend_date, subtrahend_date );

Example:

SELECT  
 MONTHS_BETWEEN( DATE'2021-07-01', DATE'2021-01-01' ) 
 FROM   DUAL;

Output:

7.TO_CHAR

The Oracle TO_CHAR() function converts a DATE or INTERVAL value to a string in a specified date format.

The Oracle TO_CHAR() function is very useful for formatting the internal date data returned by a query in a specific date format.

syntax:


TO_CHAR(expr [, date_format] [, nslparam]);

Example:

SELECT   
TO_CHAR( sysdate, 'YYYY-MM-DD' ) 
FROM   dual

Output:

16-JUN-2021

8.TO_DATE

The Oracle TO_DATE() function converts a date literal to a DATE value.

Syntax:

TO_DATE (string, format, nls_language)

Example:

SELECT TO_DATE( '16 Jun 2021', 'DD MON YYYY' )
FROM
  dual;

Output:

16-JUN-2021

TIMESTAMP Related Functions:

CURRENT_TIMESTAMP

The Oracle CURRENT_TIMESTAMP function returns the current date and time in session time zone.

the CURRENT_TIMESTAMP function returns a value of TIMESTAMP WITH TIME ZONE while the CURRENT_DATE function returns a value of DATE without time zone data.

syntax:

CURRENT_TIMESTAMP

Example:

SELECT
  CURRENT_TIMESTAMP
FROM
  DUAL;

It will return the date with time of you current time zone.

LOCALTIMESTAMP

SELECT

LOCALTIMESTAMP,    CURRENT_TIMESTAMP

FROM   dual;

It will print local timestamp .

SYSTIMESTAMP

The Oracle SYSTIMESTAMP function returns a TIMESTAMP WITH TIME ZONE value that represents the system date and time including fractional seconds and time zone.

SELECT
  SYSTIMESTAMP
FROM
  dual;

TIMEZONE Related Functions:

DBTIMEZONE

The Oracle DBTIMEZONE function returns the database time zone value.

SELECT
  DBTIMEZONE
FROM
  dual;

SESSIONTIMEZONE

The Oracle SESSIONTIMEZONE function returns the time zone of the current session.

SELECT
  SESSIONTIMEZONE
FROM
  dual;

i will update remaining function later.

thank you for reading.