IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi Folks,
Datetime functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.
Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.
The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.
Check out the list of DateTime functions
| ADD_MONTHS(date, months) | ADD_MONTHS returns the date date plus integer months. |
SQL> select ADD_MONTHS(sysdate, 1) "ADD_MONTHS" from dual; ADD_MONTH --------- 26-MAY-20
| CURRENT_DATE | CURRENT_DATE returns the current date in the session time zone |
SQL> select CURRENT_DATE from dual; CURRENT_D --------- 26-APR-20
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP returns the current date and time in the session time zone |
SQL> select CURRENT_TIMESTAMP FROM DUAL; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 26-APR-20 05.29.32.401933 PM -04:00
| DBTIMEZONE | DBTIMEZONE returns the value of the database time zone. |
SQL> select DBTIMEZONE from dual; DBTIME ------ +00:00
| EXTRACT(expres1 from date) | EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. |
SQL> select extract(year from sysdate) "year", 2 extract(month from sysdate) "month", 3 extract(day from sysdate) "day" 4 from dual; year month day ---------- ---------- ---------- 2020 4 26
| FROM_TZ(timestamp, timezone) | FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. |
SQL> SELECT FROM_TZ(TIMESTAMP '2020-04-26 08:00:00', '3:00') FROM DUAL; FROM_TZ(TIMESTAMP'2020-04-2608:00:00','3:00') --------------------------------------------------------------------------- 26-APR-20 08.00.00.000000000 AM +03:00
| LAST_DAY(date) | LAST_DAY returns the date of the last day of the month that contains date. |
SQL> select LAST_DAY(sysdate) from dual; LAST_DAY( --------- 30-APR-20
| LOCALTIMESTAMP | LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. |
SQL> select LOCALTIMESTAMP from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 26-APR-20 05.53.22.545027 PM
| MONTHS_BETWEEN(date1, date2) | MONTHS_BETWEEN returns number of months between dates date1 and date2. |
SQL> select MONTHS_BETWEEN(sysdate+365, sysdate) MONTHS_BETWEEN from dual; MONTHS_BETWEEN -------------- 12
| NEW_TIME(date, timezone1, timezone2) | NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. |
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> SELECT NEW_TIME(TO_DATE('04-26-20 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time" FROM DUAL;
New Date and Time
--------------------
25-APR-2020 21:23:45
| NEXT_DAY(date, char) | NEXT_DAY returns the date of the first weekday named by char that is later than the date date. |
SQL> select NEXT_DAY(sysdate, 'FRIDAY') "NEXT_DAY" from dual; NEXT_DAY -------------------- 01-MAY-2020 18:44:39
| NUMTODSINTERVAL(n, ‘interval_unit’) | NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. |
SQL> select NUMTODSINTERVAL(10, 'hour') "NUMTODSINTERVAL" from dual; NUMTODSINTERVAL --------------------------------------------------------------------------- +000000000 10:00:00.000000000
| NUMTOYMINTERVAL(n, ‘interval_unit’) | NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. |
SQL> select NUMTOYMINTERVAL(1,'year') "NUMTOYMINTERVAL" from dual; NUMTOYMINTERVAL --------------------------------------------------------------------------- +000000001-00
| ROUND(date, fmt) | ROUND returns date rounded to the unit specified by the format model fmt. If you omit fmt, then date is rounded to the nearest day. |
SQL> select ROUND(sysdate, 'year') "round date" from dual; round date -------------------- 01-JAN-2020 00:00:00
| SESSIONTIMEZONE | SESSIONTIMEZONE returns the time zone of the current session. |
SQL> SELECT SESSIONTIMEZONE FROM DUAL; SESSIONTIMEZONE --------------------------------------------------------------------------- -04:00
| SYS_EXTRACT_UTC(datetime_with_timezone) | SYS_EXTRACT_UTC extracts the UTC (Coordinated Universal Time–formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name. |
SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2020-04-26 11:30:00.00 -08:00') "SYS_EXTRACT_UTC" FROM DUAL; SYS_EXTRACT_UTC --------------------------------------------------------------------------- 26-APR-20 07.30.00.000000000 PM
| SYSDATE | SYSDATE returns the current date and time set for the operating system on which the database resides. |
SQL> SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL; NOW ------------------- 04-26-2020 20:42:59
| TO_CHAR (datetime) | TO_CHAR (datetime) converts a datetime or interval value 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. If you omit fmt, then date is converted to a VARCHAR2 value as follows: |
SQL> select sysdate, to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss') from dual; SYSDATE TO_CHAR(SYSDATE,'DD-MON-YYYYH -------------------- ----------------------------- 26-APR-2020 20:54:54 26-apr-2020 20:54:54
| TO_TIMESTAMP(char, fmt) | TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype. |
SQL> SELECT TO_TIMESTAMP ('26-May-20 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') "TO_TIMESTAMP" from dual;
TO_TIMESTAMP
---------------------------------------------------------------------------
26-MAY-20 02.10.10.123000000 PM
| TO_TIMESTAMP_TZ(char, fmt) | TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype. |
SQL> SELECT TO_TIMESTAMP_TZ ('26-May-20 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') "TO_TIMESTAMP" from dual;
TO_TIMESTAMP
---------------------------------------------------------------------------
26-MAY-20 02.10.10.123000000 PM -04:00
| TO_DSINTERVAL(char) | TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value. |
SQL> select TO_DSINTERVAL('100 10:00:00') from dual;
TO_DSINTERVAL('10010:00:00')
---------------------------------------------------------------------------
+000000100 10:00:00.000000000
| TRUNC(date, fmt) | The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, then date is truncated to the nearest day. |
SQL> SELECT TRUNC(TO_DATE('26-May-20','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;
New Year
--------------------
01-JAN-2020 00:00:00
Please write down in the comments any further questions you may have. I’ll be happy to help you.
One comment
Pingback: Alter Session Set NLS_DATE_FORMAT in Oracle with Examples