Oracle/SQL TO_TIMESTAMP Function

SQL To_TimeStamp FunctionOracle 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”


Tagged , , , , , , , , , , , . Bookmark the permalink.