The TO_TIMESTAMP function converts text data to a value of TIMESTAMP data type.
TIMESTAMP
TO_TIMESTAMP(char-exp [, fmt [ 'nlsparam' ] ])
A text expression that you wan to convert.
Specifies the datetime format template to be used to convert the text expression. See Table 9-4, "Datetime Format Elements" for more information about the datetime format template.
When you omit this argument, the function uses the default format of TIMESTAMP for the object.
Specifies the language in which month and day names and abbreviations are returned. This argument can have this form:
'NLS_DATE_LANGUAGE = language'
If you omit this argument, then this function uses the default date language for your session.
Example 8-143 Converting To and Extracting FromTimestamp Data
SHOW NLS_DATE_FORMAT
DD-MON-RR
SHOW TO_TIMESTAMP('10-SEP-0614:10:10:10.123000' 'DD MON RR HH24:MI:SS.FF')
10-SEP-14 10.10.10.123 AM
SHOW TO_TIMESTAMP_TZ ('2006-03-26 7:33:00 -4:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')
26-MAR-06 07.33.00 AM -04:00
DEFINE mytimestamp VARIABLE TIMESTAMP
DEFINE mytimezone VARIABLE TEXT
DEFINE mytimestamptz VARIABLE TIMESTAMP_TZ
mytimestamp = '26-MAR-06'
mytimezone = '-04:00'
mytimestamptz = FROM_TZ (mytimestamp mytimezone)
REPORT mytimestamptz
MYTIMESTAMPTZ
------------------------------
26-MAR-06 12.00.00 AM -04:00
SHOW EXTRACT (TIMEZONE_HOUR FROM mytimestamptz)
-4.00