IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I will explain Oracle TO_DATE | Convert Datetime to Date in this post.
Oracle TO_DATE
TO_DATE converts characters ( char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype ) to date format.
TO_DATE(char [, ‘format’])
For example; you can review the following conversions.
SELECT TO_DATE(
'January 11, 2021, 11:00 A.M.',
'Month dd, YYYY, HH:MI A.M.',
'NLS_DATE_LANGUAGE = American')
FROM DUAL;
TO_DATE('
---------
11-JAN-21
SELECT
TO_NUMBER ('2000'),
SYSDATE,
TO_DATE ('18/07/2014', 'dd.mm.yyyy'),
TO_DATE ('20140315', 'yyyymmdd'),
TO_DATE ('070910', 'MMDDYY')
FROM DUAL;
TO_DATE Conversion
to_date function formats as follows.
| Element | Specify in TO_DATE? | Meaning |
|---|---|---|
| – / , . ; : ‘text’ | Yes | Punctuation and quoted text is reproduced in the result. |
| AD A.D. | Yes | AD indicator with or without periods. |
| AM A.M. | Yes | Meridian indicator with or without periods. |
| BC B.C. | Yes | BC indicator with or without periods. |
| CC SCC | No | One greater than the first two digits of a four-digit year; “S” prefixes BC dates with “-“. For example, ’20’ from ‘1900’. |
| D | Yes | Day of week (1-7). |
| DAY | Yes | Name of day, padded with blanks to length of 9 characters. |
| DD | Yes | Day of month (1-31). |
| DDD | Yes | Day of year (1-366). |
| DY | Yes | Abbreviated name of day. |
| E | No | Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
| EE | No | Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
| HH | Yes | Hour of day (1-12). |
| HH12 | No | Hour of day (1-12). |
| HH24 | Yes | Hour of day (0-23). |
| IW | No | Week of year (1-52 or 1-53) based on the ISO standard. |
| IYY IY I | No | Last 3, 2, or 1 digit(s) of ISO year. |
| IYYY | No | 4-digit year based on the ISO standard. |
| J | Yes | Julian day; the number of days since January 1, 4712 BC. Number specified with ‘J’ must be integers. |
| MI | Yes | Minute (0-59). |
| MM | Yes | Month (01-12; JAN = 01) |
| MON | Yes | Abbreviated name of month. |
| MONTH | Yes | Name of month, padded with blanks to length of 9 characters. |
| PM P.M. | No | Meridian indicator with or without periods. |
| Q | No | Quarter of year (1, 2, 3, 4; JAN-MAR = 1) |
| RM | Yes | Roman numeral month (I-XII; JAN = I). |
| RR | Yes | Given a year with 2 digits, returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50; returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50. |
| RRRR | Yes | Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don’t want this functionality, simply enter the 4-digit year. |
| SS | Yes | Second (0-59). |
| SSSSS | Yes | Seconds past midnight (0-86399). |
| WW | No | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
| W | No | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
| Y,YYY | Yes | Year with comma in this position. |
| YEAR SYEAR | No | Year, spelled out; “S” prefixes BC dates with “-“. |
| YYYY SYYYY | Yes | 4-digit year; “S” prefixes BC dates with “-“. |
| YYY YY Y | Yes | Last 3, 2, or 1 digit(s) of
|
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
Do you want to learn Oracle SQL for Beginners, then read the following articles.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course
I wish to convert the default oracle basic datetime to date. Could you please let me know the command
thanks