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....



LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
 
paquicubaConnect With a Mentor Commented:
What Oracle version are you using?
Keep in mind that timestamp, timestamp with time zone, timestamp with local time zone, interval day to second and interval year to month datatypes were introduced in Oracle9i.
0
 
konektorCommented:
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
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
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






0
 
BILL CarlisleAPEX DeveloperAuthor Commented:

What if I have a table with a TIMESTAMP datatype?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.