Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Date format is unreadable

RE: Date format is unreadable

From: Whittle Jerome Contr NCI <Jerome.Whittle_at_scott.af.mil>
Date: Wed, 05 Mar 2003 13:40:59 -0800
Message-ID: <F001.005616EA.20030305134059@fatcity.com>


David,

It's not a date column. Rather it's a number column that's reading as scientific notation. Some databases, like Access, actually store dates as numbers; however, AFAIK Oracle does not.

        Those are some large numbers. 1.0397E+12 = 1039700000000

You could change your SQL to add

    COLUMN TIME_STAMP FORMAT A15
just before it to widen things out some.

Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Nguyen, David M [SMTP:david.m.nguyen_at_xo.com]
>
>
> I try to query a report to check user activity but I could not read date column. Is there a way to reformat the column to be readable? Date colum calls "time_stamp" as shown below.
>
> SQL> desc user_activity_log
>
> Name Null? Type
> ----------------------------------------- -------- --------------
> TIME_STAMP NOT NULL NUMBER(20)
> NODE_NAME NOT NULL VARCHAR2(20)
> USER_NAME NOT NULL VARCHAR2(20)
> CLIENT_IP NOT NULL VARCHAR2(15)
> DESCRIPTION NOT NULL VARCHAR2(2048)
>
> SQL> select user_name,client_ip,node_name,time_stamp from user_activity_log
> where user_name = 'admin'
> order by time_stamp;
>
> USER_NAME CLIENT_IP NODE_NAME TIME_STAMP
> -------------------- --------------- -------------------- ----------
> admin NA Insight Server 1.0397E+12
> admin NA Insight Server 1.0402E+12
> admin NA Insight Server 1.0433E+12
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Mar 05 2003 - 15:40:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US