TO_TIMESTAMP

Oracle

TO_TIMESTAMP(‘char [, ‘format’ [, ‘nlsparam’ ] ])

• ‘char’: This is the literal string that will be converted to an Oracle TIMESTAMP type. The string should represent a valid date and time.
• ‘format’: Specifies the format that the ‘char’ string is in. This uses traditional date format models, with some additional flexibility to handle the fractional second precision up to nine decimal places that is available in the TIMESTAMP type.
• ‘nlsparam’: This is an optional parameter that specifies the language in which month and day names and abbreviations are returned. It defaults to the value of the NLS_DATE_LANGUAGE parameter. This helps to deal with localization.

Example

SELECT TO_TIMESTAMP('2022-01-01 11:30:45', 'YYYY-MM-DD HH:MI:SS') FROM dual;

Output

01-JAN-22 11.30.45.000000000

Explanation

The TO_TIMESTAMP function converts a string into a timestamp. In the example, the string ‘2022-01-01 11:30:45’ is being converted to a timestamp according to the format ‘YYYY-MM-DD HH:MI:SS’. The function is being called on the dummy table dual which is used for select operations where actually no data is retrieved from a table.

PostgreSQL

TO_TIMESTAMP(text, text) RETURNS timestamp with time zone

• text: This is the string that represents a timestamp value. The string input should be in a format compatible with the defined or implied date format model.
• text: This refers to the format mask that will be applied to the timestamp value. The format mask provides instructions on how the timestamp string value should be parsed and converted to a timestamp with time zone data type. It can contain multiple format model codes that specify the format of different parts of the timestamp.

Example

SELECT TO_TIMESTAMP('30 DEC 2000', 'DD MON YYYY');

Output

"2000-12-30 00:00:00"

Explanation

The example takes a string 30 DEC 2000 and formats it into a timestamp 2000-12-30 00:00:00 using the TO_TIMESTAMP function. The format mask ‘DD MON YYYY’ is used to interpret the string.