Here is the list of Oracle Date Functions with their descriptions and quick examples.
Oracle Date Functions
| Function | Description | Example |
|---|---|---|
| ADD_MONTHS | The ADD_MONTHS procedure returns the input date plus or minus the number of months specified | Select Add_Months (’12-Jan-2018′, 3) From Dual;
Output: 12-Apr-2018 |
| CURRENT_DATE | CURRENT_DATE returns the current date in the session time zone, in value in the Gregorian calendar of datatype DATE. | Select Current_Date From Dual;
Output: 03/09/2018 20:00:03 |
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. | Select Current_Timestamp From Dual;
Output: 03/09/2018 |
| DBTIMEZONE | The DBTIMEZONE function returns the value of the database time zone. | Select Dbtimezone From Dual;
Output: +08:00 |
| EXTRACT (datetime) | An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. | Select Extract(Year From Date ‘2018-03-07’) From Dual;
Output: 2018 |
| FROM_TZ | The FROM_TZ function converts a timestamp value at a time zone to a TIMESTAMP WITH TIME ZONE value. | Select From_Tz(Timestamp ‘2018-04-12 08:00:00’, ‘3:00’) From Dual;
Output: 28/03/2000 |
| LAST_DAY | The LAST_DAY function returns the last day of the month for the specified date. | Select Last_Day (’12-Jan-2018′) From Dual;
Output: 31/01/2018 |
| LOCALTIMESTAMP | The LOCALTIMESTAMP function returns the current date and time in the session time zone in a value of datatype TIMESTAMP. | Select Localtimestamp From Dual;
Output: 04/09/2018 |
| MONTHS_BETWEEN | The MONTHS_BETWEEN function accepts two dates and returns the number of months between them. | Select Months_Between (’31-Dec-2018′, ’31-Mar-2018′) From Dual;
Output: 9 |
| NEW_TIME | The NEW_TIME function generates a new date based on a given date and time zone and an input time zone. | Select To_Char (
New_Time (To_Date (‘01152018 12:30 Am’, ‘Mmddyyyy Hh:Mi Output: January 14, 2018 09:30 Pm |
| NEXT_DAY | NEXT_DAY returns the date of the first weekday named by char that is later than the date. | Select Next_Day (’01-Sep-2018′, ‘Monday’) From Dual;
Output: 03/09/2018 |
| NUMTODSINTERVAL | NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. | Select Numtodsinterval (22, ‘Day’) From Dual;
Output: +22 00:00:00.000000 |
| NUMTOYMINTERVAL | NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. | Select NUMTOYMINTERVAL (8, ‘MONTH’) FROM DUAL;
Output: +00-08 |
| ROUND (date) | The ROUND function rounds a date value to the nearest date as specified by a format mask. | SELECT ROUND (TO_DATE (’02sep2018′), ‘W’) FROM DUAL;
OUTPUT: 01/09/2018 |
| SESSIONTIMEZONE | The SESSIONTIMEZONE function returns the value of the current session’s time zone. | Select Sessiontimezone From Dual;
Output: +08:00 |
| SYS_EXTRACT_UTC | The SYS_EXTRACT_UTC function extracts the UTC (Coordinated Universal Time–formerly Greenwich Mean Time) from a datetime with time zone displacement. | Select Sys_Extract_Utc(Timestamp ‘2018-03-28 11:30:00.00 -08:00’) From Dual;
Output: 28/03/2018 19:30:00.000000000 |
| SYSDATE | The SYSDATE function returns the current system date and time. | Select Sysdate From Dual;
Output: 03/09/2018 21:22:12 |
| SYSTIMESTAMP | The SYSTIMESTAMP function returns the system date, including fractional seconds and time zone of the database. | Select Systimestamp From Dual;
Output: 03/09/2018 |
| TO_CHAR (datetime) | TO_CHAR converts the date of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. | Select To_Char (Sysdate, ‘Month Dd, Yyyy’) From Dual;
Output: September 03, 2018 |
| TO_TIMESTAMP | TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype. | Select To_Timestamp (‘2018-12-01 11:00:00’, ‘Yyyy-Mm-Dd Hh:Mi:Ss’) From Dual;
Output: 01/12/2018 11:00:00.000000000 |
| TO_TIMESTAMP_TZ | TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype. | Select To_Timestamp_Tz(‘2018-12-01 11:00:00 -8:00’,‘Yyyy-Mm-Dd Hh:Mi:Ss Tzh:Tzm’) From Dual;
Output: 01/12/2018 |
| TO_DSINTERVAL | TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND type. | Select To_Dsinterval (‘100 10:00:00’) From Dual;
Output: +100 10:00:00.000000 |
| TO_YMINTERVAL | The TO_YMINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted. | Select To_Yminterval(’01-02′) From Dual;
Output: +01-02 |
| TRUNC (date) | The TRUNC function truncates a date as specified by a format mask. | SELECT TRUNC (SYSDATE, ‘Mm’) FROM DUAL;
Output: 01/09/2018 |
| TZ_OFFSET | TZ_OFFSET returns the time zone offset corresponding to the value entered based on the date the statement execution. | SELECT TZ_OFFSET(‘US/EASTERN’) Offset FROM Dual;
Output: -04:00 |
