Question

I wish to view my column time as hh24:mi:ss but even i insert it as to_date( 13:00:00 , hh24:mi:ss ). It still displays as date format. I have tried different methods to convert and i don t wish to alter the session.

我在此提出的表格:

CREATE TABLE Test
  (
    Name VARCHAR2(20),
    Description Varchar2(20),
    StudyTime Date,
    SleepTime Date,
  );

插入:

INSERT INTO Test 
VALUES( JUDY , STUDYING AT ABC , (To_Date( 01:00:00 ,  HH24:MI:SS )), 
            (TO_DATE( 06:35:00 ,  HH:MI:SS )));

显示结果:

Judy,Studying at abc,11-Nov-2011,11-Nov-2011

感谢任何人帮助!

Answer 1

Your IDE or SQL*Plus settings are displaying the default date format of DD-Mon-YYYY. Even though you are entering just a time format, oracle will still see it as a full date and time, hence the 11-Nov-2011 date (you need to be aware of this!).

When you then select all columns whichever interface you are using to display the results will show you the character representation of the date columns, if you have not specified what that is it will use the default NLS format settings. See: http://www.adp-gmbh.ch/ora/admin/nls_levels.html

要么修改国家最低生活水平标准格式,要么具体使用TO_CHAR。

Try:

SELECT name,
       description,
       TO_CHAR(studytime,  HH24:MI:SS ) as studytime,
       TO_CHAR(sleeptime,  HH:MI:SS ) as sleeptime
  FROM test;

N.B. I used HH24 for study and H for sleeptime,与你在进入贵方价值观时所做的那样。

这种做法应该以正确的格式予以取消。

友情链接