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.
Comments
Post a Comment