Oracle TO_DATE | Convert Datetime to Date

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.

ElementSpecify in TO_DATE?Meaning
– / , . ; : ‘text’YesPunctuation and quoted text is reproduced in the result.
AD A.D.YesAD indicator with or without periods.
AM A.M.YesMeridian indicator with or without periods.
BC B.C.YesBC indicator with or without periods.
CC SCCNoOne greater than the first two digits of a four-digit year; “S” prefixes BC dates with “-“. For example, ’20’ from ‘1900’.
DYesDay of week (1-7).
DAYYesName of day, padded with blanks to length of 9 characters.
DDYesDay of month (1-31).
DDDYesDay of year (1-366).
DYYesAbbreviated name of day.
ENoAbbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EENoFull era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
HHYesHour of day (1-12).
HH12NoHour of day (1-12).
HH24YesHour of day (0-23).
IWNoWeek of year (1-52 or 1-53) based on the ISO standard.
IYY IY INoLast 3, 2, or 1 digit(s) of ISO year.
IYYYNo4-digit year based on the ISO standard.
JYesJulian day; the number of days since January 1, 4712 BC. Number specified with ‘J’ must be integers.
MIYesMinute (0-59).
MMYesMonth (01-12; JAN = 01)
MONYesAbbreviated name of month.
MONTHYesName of month, padded with blanks to length of 9 characters.
PM P.M.NoMeridian indicator with or without periods.
QNoQuarter of year (1, 2, 3, 4; JAN-MAR = 1)
RMYesRoman numeral month (I-XII; JAN = I).
RRYesGiven 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.
RRRRYesRound 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.
SSYesSecond (0-59).
SSSSSYesSeconds past midnight (0-86399).
WWNoWeek of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
WNoWeek of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
Y,YYYYesYear with comma in this position.
YEAR SYEARNoYear, spelled out; “S” prefixes BC dates with “-“.
YYYY SYYYYYes4-digit year; “S” prefixes BC dates with “-“.
YYY YY YYesLast 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_DATECURRENT_DATE returns the current date in the session time zone
SQL> select CURRENT_DATE from dual;

CURRENT_D
---------
26-APR-20

 

 

 

CURRENT_TIMESTAMPCURRENT_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

 

 

 

 

 

DBTIMEZONEDBTIMEZONE 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

 

 

 

LOCALTIMESTAMPLOCALTIMESTAMP 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

 

 

 

SESSIONTIMEZONESESSIONTIMEZONE 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

 

 

 

SYSDATESYSDATE 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

 811 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply