Oracle TO_DATE | Convert Datetime to Date

Mehmet Salih Deveci

3 years ago

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