Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2001

    Unhappy Unanswered: select current_timestamp returns invalid column name error

    Hi all,

    I'm pretty much a newbie with Oracle, and I've got a question that a guru would probably laugh at, but I can't resolve!

    When running the query SELECT CURRENT_TIMESTAMP FROM DUAL; I'm getting the error ORA-00904: invalid column name.

    Could anyone please advise why this is happening to me (apart from my bad karma) and what I can do to make the select work?

    Any answer much appreciated


  2. #2
    Join Date
    Feb 2002
    Hello Megan, sounds like you normally work with DB2.

    Try 'select sysdate from dual'

    to convert to different time/date formats you can either alter your session or use the to_char function to convert to a time/date string.


    alter session set nls_date_format = 'DD-MM-YYYY HH24:MIS';


    select to_char(sysdate, 'DD-MM-YYYY HH24:MIS') from dual;

    Oracle only has one date/time data type (DATE). It combines date and time/milliseconds). DB2 (as you probably know) has time, date and timestamp data types.

    Hope this helps!


  3. #3
    Join Date
    Apr 2002
    California, USA


    Originally posted by nekelly
    Hello Megan, sounds like you normally work with DB2.
    I would say she normally work with 8i and trying to explore the new features of 9i:

    SQL*Plus: Release - Production on Thu May 9 09:14:53 2002

    (c) Copyright 2001 Oracle Corporation. All rights reserved.

    Connected to:
    Oracle9i Enterprise Edition Release - Production
    JServer Release - Production

    SQL> select current_timestamp from dual;

    09-MAY-02 AM -07:00


    Check the latest Oracle DBA forums at:

    Hope that helps,

  4. #4
    Join Date
    Oct 2001
    Hi Noel and clio_usa,

    Thanks very much for your responses.

    We're using 8.1.7, and I've discovered that the CURRENT_TIMESTAMP function is a new feature in 9i. The error was raised by an outside party who has obviously done some training with 9i and was trying to run the select on an 8i version and wanted to know why he was getting an error.

    I've never used the function (of course!) and wasn't sure why it wouldn't work either, when I could see the syntax and function was valid from searching the net. However, none of the sites I went to seemed to specify that it's new with 9i....

    Noel, the to_char option works like a charm in returning date and time in 8i.
    Once again, thanks for responding so quickly!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts