DATE() functions in Oracle SQL

SQL date functions are used to handle the data of the date easily and more effectively according to the conditions. The format of the DATE in the oracle SQL is 'YY: MM: DD'. There are many DATE() functions in the oracle. The commonly used oracle SQL DATE() functions are as follows.

  • ADD_MONTHS
  • CURRENT_DATE
  • EXTRACT
  • LAST_DAY
  • MONTHS_BETWEEN
  • TO_DATE

This function adds the number of months to the given month. The return type is always DATE, regardless of the datatype of date.

Syntax of the ADD_MONTH() function :

SELECT ADD_MONTHS (DATE 'YY-MM-DD', n)
FROM DUAL;

Where,

  • YY stands for the value of the year.
  • MM stands for the value of the month.
  • DD stands for the value of the day.
  • n stands for the number of months to be added.

Example of the ADD_MONTH() function :

SELECT ADD_MONTHS (DATE '2018-7-19', 5)
FROM DUAL;

date-func-1
Example of the ADD_MONTH() function when 'n' value is negative :

SELECT ADD_MONTHS (DATE '2018-7-19', -5)
FROM DUAL;

date-func-2
The function brings the date backward when the 'n' value is negative.

CURRENT_DATE function in Oracle SQL

This function returns the current date. This function doesn't require any condition or doesn't require any arguments. The CURRENT_DATE function in the Oracle SQL returns a DATE value in the Gregorian calendar.

SYSDATE is also a date function in Oracle SQL. This function plays the same role as the CURRENT_DATE does. The main difference between CURRENT_DATE and SYSDATE is CURRENT_DATE returns the date from your session timezone (your timezone). SYSDATE returns the date from the database timezone.
database timezone:

Syntax of the CURRENT_DATE function :

CURRENT_DATE

The only query to return the CURRENT_DATE is :

SELECT CURRENT_DATE FROM DUAL;

CURRENT-DATE

The only query to return the database date using the SYSDATE function :

SELECT SYSDATE FROM DUAL;

sysdate

EXTRACT() function in Oracle SQL

This function is used to extract specific component values like YEAR, MONTH, DAY from a DATE value.

Syntax of EXTRACT() function :

SELECT EXTRACT(field FROM source) FROM DUAL;

Example of EXTRACT() function :

SELECT EXTRACT( MONTH FROM TO_DATE( '31-march-1999',  'DD-Mon-YYYY HH' ) ) MONTH
FROM DUAL;
                                 [ OR ]
SELECT EXTRACT(MONTH FROM DATE '1999-3-31')
FROM DUAL; 

extract-date
The month is extracted from the given date.

LAST_DAY() function in Oracle SQL

The LAST_DAY() function returns the last day of the month that contains a date. The return type is always DATE, regardless of the datatype of date.

Syntax of LAST_DAY() function in SQL:

SELECT LAST_DAY( DATE) FROM DUAL;

Example of LAST_DAY() function in SQL:

SELECT LAST_DAY( DATE '2000-07-01') 
FROM DUAL;

LAST-DATE
In the above query, the last day of July month is retrieved.

MONTHS_BETWEEN() function in Oracle SQL

This function returns the number of months between two dates (date1, date2). If the date1 is later than the date2, then the result is positive. If date2 is later than date1, then the result is negative.

Syntax of MONTHS_BETWEEN() function :

SELECT MONTHS_BETWEEN (date1, date2) FROM DUAL;

Example of MONTHS_BETWEEN() function when date1 is later than date2 :

SELECT MONTHS_BETWEEN( DATE '2018-07-09', DATE '2017-01-01' )
FROM DUAL;

months_between
In the above query, the month is represented by the positive value.

Example of MONTHS_BETWEEN() function when date2 is later than date1 :

SELECT MONTHS_BETWEEN( DATE '2017-07-09', DATE '2018-01-01' )
FROM DUAL;

months-between-1
In the above query, the month is represented by the negative value.

(calculation of months)

TO_DATE() function in Oracle SQL

The Oracle TO_DATE() function converts any date format to a specified date format [DD-MM-YY].

Syntax of the TO_DATE() function :

TO_DATE (string, format, nls_language)
  • String: it is a string value that converts to a DATE value.
  • Format : Is the date and time format for the string. The format argument is optional. If you omit the format, the string must be in the standard date format which is DD-MM-YY e.g., 01-01-2020.
  • nls_language: is an expression that specifies the language for day and month names in the string. This value is used to determine how the output value is displayed. the nls_language is optional.

Example of the TO_DATE() function :

SELECT TO_DATE( '2017 Jan 20', 'YYYY MON DD' )
FROM dual;

to_date-1
In the above query, the date is represented according to the format 'DD- MON- YY'.

Example 2 :

SELECT TO_DATE( '22 Jan 2020', 'DD MON YYYY' )
FROM dual;

TO-DATE-2
In the above query, the date is represented according to the format 'DD -MON- YY'.

TO_DATE() Conversion function in Oracle SQL:

Conversions functions are used to convert from one data type to another type. The TO_DATE function converts a string value to a DATE value using the specified format.

The syntax of the TO_DATE() function:

TO_DATE( charvalue, expr_format)

Example for the TO_DATE() FUNCTION :

SELECT TO_NUMBER( '22 Jan 1994', 'DD MON YYYY' )
FROM dual;

to-date
In the above query, the given values are string. Ant it is converted into DATE value in the output.

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions