When using Oracle Database, you can use the TO_CHAR(datetime) function to return various parts of a datetime value, including the fractional seconds.

To return the fractional seconds part from a datetime value, use the FF format element.

Example

Here’s an example to demonstrate:

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'FF')
FROM DUAL;

Result:

123456789

It’s also possible to limit the precision by appending a number to the FF part:

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'FF5')
FROM DUAL;

Result:

12345

The number can be 1 through 9.

Prepend the Seconds Part

In the above examples, I only returned the fractional seconds part and nothing else.

We can include the seconds part as well as the fractional seconds. To do this, use the SS. We can also use X to specify the radix character (the symbol used to separate the integer part from the fractional part):

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'SSXFF')
FROM DUAL;

Result:

37.123456789

While you could explicitly provide your own radix character – for example, a full stop (.), the X format element can be useful for porting across different languages/territories.

When I ran the above examples, my NLS_TERRITORY parameter was set to AUSTRALIA, which resulted in the radix character being a full stop.

Here’s what happens when I change my NLS_TERRITORY parameter to GERMANY:

ALTER SESSION SET NLS_TERRITORY = 'GERMANY';

SELECT 
    TO_CHAR(TIMESTAMP '2035-01-01 10:15:37.123456789', 'SSXFF')
FROM DUAL;

Result:

37,123456789

Now the radix character is a comma.

It’s actually the NLS_NUMERIC_CHARACTERS parameter that specifies what character is used for the radix character. However, changing the NLS_TERRITORY parameter implicitly changes the NLS_NUMERIC_CHARACTERS parameter. You can also explicitly update the NLS_NUMERIC_CHARACTERS parameter if you prefer, in which case your NLS_TERRITORY parameter will remain unchanged.

See List of Datetime Format Elements in Oracle for a full list of format elements that can be used to format datetime values.