SQL Pocket Guide, 3rd Edition

Datetime Conversions: Oracle

You can convert to and from datetime types in Oracle by using the following functions:

TO_CHAR({datetime|interval}, format)
TO_DATE(string, format)
TO_TIMESTAMP(string, format)
TO_TIMESTAMP_TZ(string, format)
TO_DSINTERVAL('D HH:MI:SS')
TO_YMINTERVAL('Y-M')
NUMTODSINTERVAL(number, 'unit_ds')
NUMTOYMINTERVAL(number, 'unit_ym')

unit_ds ::= {DAY|HOUR|MINUTE|SECOND}
unit_ym ::= {YEAR|MONTH}

The format argument allows great control over text representation. For example, you can specify precisely the display format for dates:

SELECT name,
   TO_CHAR(confirmed_date, 'dd-Mon-yyyy') cdate
FROM upfall;

Munising Falls  08-Dec-2005
Tannery Falls   08-Dec-2005
Alger Falls     08-Dec-2005
…

And to convert in the other direction:

INSERT INTO upfall (id, name, confirmed_date)
VALUES (15, 'Tahquamenon',
        TO_TIMESTAMP('29-Jan-2006','dd-Mon-yyyy'));

Table 1-2 lists the format elements that you can use in creating a format mask. Output from many of the elements depends on your session’s current language setting (e.g., if your session language is French, you’ll get month names in French).

When converting to text, the case of alphabetic values, such as month abbreviations, is determined by the case of the format element. Thus, 'Mon' yields 'Jan' and 'Feb', 'mon' yields 'jan' and 'feb', and 'MON' yields 'JAN' and 'FEB'. When converting from text, case is irrelevant.

The format mask is always optional. You can omit it when your input value conforms to the default format specified by the following: ...