Other related articles:

Recently viewed articles:

SQL TO_TIMESTAMP Function

SQL TO_TIMESTAMP function provides same functionality provided by TO_DATE function. The only difference is that it can be used to enter fraction values for seconds. It converts a selected string to a SQL DATE format used by database. The string may be of any data type, but must conform to a valid date when converted to a date. SQL TO_TIMESTAMP function consists of two parameters, character string and SQL Date format string. The character string parameter represents the string that is to be matched, while the date format string represents the format of data in character string.

The syntax is

TO_TIMESTAMP (input string, date picture format)

Example:
The following SQL TO_TIMESTAMP query will return '8-Dec-2005 3:00:55.123 PM' as date value.

SELECT TO_TIMESTAMP('8-Dec-2005 15:00:55.123', 'dd-mon-yyyy HH24:MI:SS.FF') from dual;

Output:
sql to_timestamp image1

Note: DUAL is a dummy table used by Oracle. The SQL DATE format picture ('dd-mon-yyyy HH24:MI:SS.FF’) tells Oracle the format of input data string. The function converts the first quoted string into a date, using as a definition the second quoted string. Note the FF picture format is taking values for fractions of seconds.

The sql date format picture is composed of date format elements which are discussed in detail in following topic:

Date Format Element