oracle 甲骨文。如何输出日期和时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17223226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle. How to output date and time?
提问by Buras
My Visit table is the following:
我的访问表如下：
insert into Visit
values(12, to_date('19-JUN-13', 'dd-mon-yy'), to_date('19-JUN-13 12:00 A.M.' , 'dd-mon-yy hh:mi A.M.'));
insert into Visit
values(15, to_date('20-JUN-13', 'dd-mon-yy'), to_date('20-JUN-13 02:00 A.M.' , 'dd-mon-yy hh:mi A.M.'));
insert into Visit
values(18, to_date('21-JUN-13', 'dd-mon-yy'), to_date('21-JUN-13 10:30 A.M.' , 'dd-mon-yy hh:mi A.M.'));
When i try to query it:
select * from Visiti get:
当我尝试查询它时：
select * from Visit我得到：
SQL> select * from visit;
SLOTNUM DATEVISIT ACTUALARRIVALTIME
---------- --------- ------------------------------
12 19-JUN-13 19-JUN-13
15 20-JUN-13 20-JUN-13
18 21-JUN-13 21-JUN-13
SQL> spool off;
How come the time is not there?
怎么没时间？
回答by Ed Gibbs
You can also set a format that applies to alldates like this:
您还可以设置适用于所有日期的格式，如下所示：
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI:SS PM';
That way, your original query would output the dates in the format you're after, without using TO_CHAR. To set back to the usual default format, just do this:
这样，您的原始查询将以您所追求的格式输出日期，而无需使用TO_CHAR. 要设置回通常的默认格式，只需执行以下操作：
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
回答by Randy
that is the oracle date format that is set as the default for your instance.
这是为您的实例设置为默认值的 oracle 日期格式。
you should properly specify the format to see more or less.. something like this:
您应该正确指定格式以查看更多或更少......像这样：
select to_char( datevisit, 'dd-mon-yy hh24:mi:ss' ) from visit
回答by Alex Poole
You're relying on implicit date conversion, which is using your (session-dependent, but maybe inherited from the DB default) NLS_DATE_FORMATsetting - in this case that seems to be DD-MON-RR.
您依赖于隐式日期转换，它使用您的（依赖于会话，但可能从数据库默认值继承）NLS_DATE_FORMAT设置 - 在这种情况下，似乎是DD-MON-RR.
You can use to_charto specify the format, e.g.:
您可以使用to_char来指定格式，例如：
select to_char(actualarrivaltime, 'DD-MON-YYYY HH:M:SS PM') from ..
The datetime format models are described in the documentation.
In general it's better to never rely on implcit conversions. Even if you get what you expect now, they can be session-specific so another user in another client ,ight see something different. This can cause failures as well as just look wrong. Always specify date and number formats, using to_date, to_char, to_timestampetc.
一般来说，最好不要依赖隐式转换。即使您现在得到了您所期望的，它们也可以是特定于会话的，因此另一个客户端中的另一个用户可能会看到不同的东西。这可能会导致失败，也可能看起来是错误的。始终指定日期和数字格式，使用to_date，to_char，to_timestamp等。
回答by Bhargav B
Oracle internally follow 'DD-Mon-YY' format to store in database. So it returns'DD-Mon-Y
Oracle 内部按照 'DD-Mon-YY' 格式存储在数据库中。所以它返回'DD-Mon-Y
If you want to date format with hours min and sec. you can alter NLS_DATE_FORMAT in session.
如果你想用小时分钟和秒来日期格式。您可以在会话中更改 NLS_DATE_FORMAT。
If you want to query for the just Presentation purpose for that instance. Use TO_char Function to convert into required format.
如果您想查询该实例的 Just Presentation 目的。使用 TO_char 函数转换成需要的格式。
SELECT slotnum, TO_CHAR (datevisit, 'DD-MON-YY ') "DATEVISIT",
TO_CHAR (actualarrivaltime, 'DD-MON-YY HH:MI:SS AM') "ACTUALARRIVALTIME"
FROM visit
I Hope the above query gives you the output as you like.
我希望上面的查询给你你喜欢的输出。
回答by Prasanth Pennepalli
select localtimestamp from dual;
