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

    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

    Cheers,
    Megan

  2. #2
    Join Date
    Feb 2002
    Posts
    11
    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.

    e.g.

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

    or

    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!

    Noel.


  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    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 9.0.1.0.1 - Production on Thu May 9 09:14:53 2002

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


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

    SQL> select current_timestamp from dual;

    CURRENT_TIMESTAMP
    ---------------------------------------------------------------------------
    09-MAY-02 09.29.09.000001 AM -07:00

    SQL>


    Check the latest Oracle DBA forums at:
    http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi

    Hope that helps,


  4. #4
    Join Date
    Oct 2001
    Posts
    18
    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!

    Cheers,
    Megan

Posting Permissions

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