Date and Time Functions in SQL

Oracle has several functions that operate on "date" data types. These are some:

- add_months (f, n): adds a number of months to a date. If the second argument is positive, this number of months is added to the sent date; if it is negative, such number of months is subtracted from the sent date.

Example:

select add_months ('10 / 06/2020 ', 5) from dual; --returns "11/10/20"

select add_months ('10 / 06/2020 ', - 5) from dual; --returns "01/10/20"

select add_months ('30 / 01/2020 ', 1) from dual; - returns "02/29/20" since it is the last day of that month.

- last_day (f): returns the last day of the month of the date sent as an argument. 

Example:

 select last_day ('10 / 02/2020 ') from dual; - "02/29/20"

 select last_day ('10 / 08/2020 ') from dual; - "08/31/20"

- months_between (f1, f2): returns the number of months between the dates sent as an argument. 

Example:

select months_between ('19 / 05/2020 ',' 06/21/20 ') from dual; - returns -1.06451613 

- next_day (date, day): returns a date corresponding to the first day specified in "day" after the specified date. The following example finds the Monday following the specified date:

select next_day ('08/10/2020', 'MONDAY') from dual; - 08/17/20

- current_date: returns the current date. 

Example:

 select current_date from dual;

- current_timestamp: returns the current date

select current_timestamp from dual;

Returns: 07/22/20 07: 27: 20,973000000 AMERICA / BUENOS_AIRES

- sysdate: returns the current date and time on the Oracle server.

- systimestamp: return current date and time.

select systimestamp from dual;

Returns 07/22/20 07: 27: 45,012000000 -03: 00

- to_date: converts a string to data type "date".

 

Example:

select to_date ('05 -SEP-2019 10:00 AM ',' DD-MON-YYYY HH: MI AM ') from dual;

Returns 09/05/19

- to_char: convert a date to a character string.

Example:

select to_char ('10 / 10/2020 ') from dual;

- extract (part, date): returns the part (specified by the first argument) of a date. You can extract the year (year), month (month), day (day), hour (hour), minute (minute), second (second), etc.

Example:

select extract (month from sysdate) from dual;

returns the month number of the current date.

In Oracle: The arithmetic operators "+" (plus) and "-" (minus) can be used with dates.

For examples:

select sysdate-3: Returns 3 days before the current date.

select to_date ('15 / 12/2020 ') - 5 from dual;

Returns 12/10/20

These functions can be used by sending as an argument the name of a field of type date.