Oracle TO_TIMESTAMP Function is used to convert string type into date type.
Oracle TO_TIMESTAMP Function Syntax
SELECT TO_TIMESTAMP(string_value, [FORMAT_MASK],[NLS_LANGUAGE]) FROM table_name;
Here in the above Oracle TO_TIMESTAMP 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 – Using SQL TO_TIMESTAMP Query Example
SQL TO_TIMESTAMP Function is used with the SQL SELECT Statement.
For example, the SQL query below will return the date type.
SELECT TO_TIMESTAMP('2012-11-01 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF')
FROM dual;
May return “11/1/2012 7:15:31.123400000 AM”
Oracle TO_TIMESTAMP – Using SQL TRUNC Function Example
SQL TO_TIMESTAMP Function can be used with the SQL TRUNC Function.
For example, the SQL query with TRUNC Function below will return the date type and remove all the other components.
SELECT TRUNC(TO_TIMESTAMP('2012-11-01 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF'))
FROM dual;
May return “11/1/2012”
Oracle TO_TIMESTAMP – Using + and – Signs Example
SQL TO_TIMESTAMP Function can be used with the + and – signs.
For example, the SQL query with + sign below will return the next day’s date.
SELECT TO_TIMESTAMP('2012-11-01 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF')+1
FROM dual;
May return “11/2/2012 7:15:31.123400000 AM”