how to convert string type to timestamp type in oracle sql plsql

Oracle TO_TIMESTAMP_TZ Function is used to convert string type to time stamp type along with the time zone.

---

Oracle TO_TIMESTAMP_TZ Function Syntax

SELECT TO_TIMESTAMP_TZ(string_value, [FORMAT_MASK],[NLS_LANGUAGE])
FROM table_name;

In the above Oracle TO_TIMESTAMP_TZ Function Syntax

• STRING_VALUE is the value that will be converted into date format.
• FORMAT_MASK is an optional field, if present; it is used to convert value into time stamp.
• NLS_LANGUAGE is also an optional field, if present; it is used to convert value into time stamp.

---

Oracle TO_TIMESTAMP_TZ Function – Use with SQL Select SELECT Statement Example

Oracle TO_TIMESTAMP_TZ Function is used with the SQL SELECT Statement.

For example the below SQL query converts string type to time stamp type along with the time zone.

SELECT TO_TIMESTAMP_TZ('2012-11-01 07:15:31.1234 PST', 'YYYY-MM-DD HH24:MI:SS.FF TZR')
FROM dual;

Will return “11/1/2012 7:15:31.123400000 AM -08:00”

---

Oracle TO_TIMESTAMP_TZ Function – Using with SQL SELECT Statement Example

The below Oracle TO_TIMESTAMP_TZ query converts string type to time stamp type along with the time zone.

SELECT TO_TIMESTAMP_TZ('2012-11-01 07:15:31.1234+5:30', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
FROM dual;

Will return “11/1/2012 7:15:31.123400000 AM +05:30”

---