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
TO_TIMESTAMP not giving Full Year 2015. Even after proving YYYY mask. Why?
Hi, what does your SQL look like? It could be to do with the input data, perhaps it’s in a strange format.
HI MK RAO,
You just replace your ‘YYYY’ to ‘RR’.
This will help your problem.
thank you
how to convert varchar2 data like ‘1569996880844’ o date timestamp in oracle ?
say if i wanted to be in this format – ‘DD-MM-YYYY HH24:MM:SS’.