Oracle TO_TIMESTAMP Function with Examples

The Oracle TO_TIMESTAMP function is useful if you need to work with timestamp data types. Learn how to use it with some examples in this article.

Purpose of the Oracle TO_TIMESTAMP Function

The purpose of the Oracle TO_TIMESTAMP function is to convert a string value into a timestamp value.

Now, a TIMESTAMP value is a type of date. It includes the date, the time, and a series of decimal places to indicate fractional seconds.

Converting a string to a TIMESTAMP value is done using the TO_TIMESTAMP function.

 

Syntax

The syntax of the TO_TIMESTAMP function is:

TO_TIMESTAMP (input_string, [format_mask], ['nlsparam'] )

 

Parameters

The parameters of the Oracle TO_TIMESTAMP function are:

• input_string (mandatory): This is the input string, or the string to convert into the timestamp value.
• format_mask (optional): This string specifies the format of the input_string, as you can specify your input in many different ways. If this is not supplied, then the input_string must be in the same format as the NLS_TIMESTAMP_FORMAT parameter, which is your default format.
• nlsparam (optional): If this string is supplied, you can specify the language in which the month and day names and abbreviations are returned.

 

Can You Convert an Oracle TIMESTAMP to a CHAR?

Yes, you can, if you really need to.

You can use the TO_CHAR function, along with TRUNC, and specify the format.

SELECT TO_CHAR(TRUNC(SYSTIMESTAMP)) FROM dual;

Result:

16-09-2015

This result is in a CHAR format. You can use a similar set of functions to convert your TIMESTAMP values to CHAR.

What Format Do I Need To Supply For Oracle TO_TIMESTAMP?

The default format of the TO_TIMESTAMP function is the value of NLS_TIMESTAMP_FORMAT, which depends on your Oracle database.

The format can be something like this: ‘YYYY-MM-DD HH:MI:SS.FF’

The different formats you can supply are:

Parameter	Explanation
YYYY	Four digit year
MM	Month (01-12, where JAN = 01)
MON	Abbreviated name of month
MONTH	Name of month, padded with blanks to length of 9 characters
DD	Day of month (1-31)
HH	Hour of day (1-12)
HH12	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minute (0-59)
SS	Second (0-59)

 

Does TO_TIMESTAMP Work The Same In Oracle 10g and 11g?

Yes, it works the same.

 

Examples of the Oracle TO_TIMESTAMP Function

Here are some examples of the TO_TIMESTAMP function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1

This example uses the TO_TIMESTAMP function with the default format.

SELECT TO_TIMESTAMP('16-SEP-2015 08:10:14 AM') AS TS_TEST FROM dual;

Result:

TS_TEST

16/SEP/15 08:10:14.000000000 AM

The result is the same value, but converted to a TIMESTAMP data type.

 

Example 2

This example uses the TO_TIMESTAMP function, but we specify a format.

SELECT TO_TIMESTAMP('12-09-2015 04:32:20 PM', 'DD-MM-YYYY HH:MI:SS AM') AS TS_TEST FROM dual;

Result:

TS_TEST

12/SEP/15 04:32:20.000000000 PM

The result is the same value, but converted to a TIMESTAMP data type.

 

Example 3

This example uses the TO_TIMESTAMP function, but we specify a different format.

SELECT TO_TIMESTAMP('8 DEC 2015 10:04', 'DD MON YYYY HH:MI') AS TS_TEST FROM dual;

Result:

TS_TEST

08/DEC/15 10:04:00.000000000 AM

The result is the same value, but converted to a TIMESTAMP data type.

 

Similar Functions

Some functions which are similar to the TO_TIMESTAMP function are:

• TO_DATE: This function converts a value into a DATE data type, which is similar, but has no time component.

You can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net