Get current timestamp in specific format with seconds to one decimal place

Question

I'm trying to set a value in an Oracle database that resembles the following:

2016-06-21 03:07:25.0

It is a varchar of the current date with one digit after the decimal for seconds. I tried multiple ways to achieve this but to no avail:

SQL> select sysdate from dual;

SYSDATE
--------
21-06-16

From this I tried:

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS.F';
ERROR:
ORA-01821: date format not recognized

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS.s';
ERROR:
ORA-01821: date format not recognized

Then I tried:

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual;

TO_CHAR(SYSDATE,'YY
-------------------
2016-06-21 12:58:55

Since that worked up to the seconds I tried the decimal part:

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS.SSSS') from dual;

TO_CHAR(SYSDATE,'YYYY-MM
------------------------
2016-06-21 13:23:47.4747

So I can get 4 significant places. Now I try with just 1:

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS.S') from dual;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS.S') from dual
                       *
ERROR at line 1:
ORA-01821: date format not recognized

So how can I get the current timestamp in the abovementioned format with seconds set to one decimal place?

Answer 1

SSSS isn't fractional seconds. It's just repeating the SS value twice; in your example you get 13:23:47.4747 - the 4747 is just 47 displayed twice. Maybe you were thinking of SSSSS, but that is the number of seconds since midnight (i.e. 0-86399) so isn't useful here either. A single S isn't a valid format model element.

Dates don't have fractional second precision. A single F isn't a valid format model element either, but even FF isn't valid for a date.

You need to use systimestamp to get the server time with fractional seconds, rather than sysdate:

select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF1') from dual;

TO_CHAR(SYSTIMESTAMP,'YYYY-MM
-----------------------------
2016-06-21 14:42:22.7        

Storing a date/time as a string is generally a bad idea. I'd suggest you make the column timestamp(1) which will keep the one-decimal-place precision when you set it from systimestamp. You can see what it would get with cast:

select to_char(cast(systimestamp as timestamp(1)), 'YYYY-MM-DD HH24:MI:SS.FF9') from dual;

TO_CHAR(CAST(SYSTIMESTAMPASTI
-----------------------------
2016-06-21 14:49:09.900000000

Converting a date/time to a string should really only be done for display, not for storage.