asked on

ORA-01821: date format not recognized -- SELECT TO_CHAR(SYSDATE,'HH:MI:SS.FF') FROM sys.dual;

Hi,
I have the following:
select to_char(sysdate,'hhmiss') into v_time from dual;
g_logFileName := 'bcarldebug'||v_time ||'.dbg';

Trying to add hundereds to my file names to not overwrite ones that are written already.

I tried the following: (different times...)
      SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
      Session altered.

      start_time TIMESTAMP(4);
      PLS-00201: identifier 'TIMESTAMP' must be declared

      SELECT TO_CHAR(SYSDATE,'HH:MI:SS.FF') FROM sys.dual;
      ORA-01821: date format not recognized

1 SELECT TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')
2* FROM dual
SQL> /
SELECT TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')
*
ERROR at line 1:
ORA-00904: invalid column name

      Gave up....

konektor

u cannot use sysdate to obtain time in ff format
this works :
SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;

SELECT TO_TIMESTAMP ('1999-12-01 11:00:00.1234', 'YYYY-MM-DD HH:MI:SS.FF4') FROM DUAL

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BILL Carlisle

ASKER

No way to do it with 8.1.7 ???

Bill :>)

konektor:
I GET ERROR....

SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;
SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual
*
ERROR at line 1:
ORA-00904: invalid column name

paquicuba:

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

BILL Carlisle

ASKER

What if I have a table with a TIMESTAMP datatype?