TO_TIMESTAMP
TO_TIMESTAMP is a function in SQL that converts a string value into a valid timestamp value, allowing for the manipulation and analysis of temporal data. This function is particularly useful when the data contains timestamp strings that need to be manipulated as a part of the SQL query operation.
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.000000000Explanation
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.
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.