如何在 Oracle 数据库中格式化 DATETIME？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28140306/
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
How Can I format a DATETIME in Oracle Database?
提问by ?erny Jan
I need to save datetime in this columns in this format and then it will be seen in this format if i write select TIME from GAMES. But this is wrong. Can you help me ?
我需要以这种格式在此列中保存日期时间，然后如果我从 GAMES 中写入 select TIME，它将以这种格式显示。但这是错误的。你能帮助我吗 ？
create table GAMES (
CIS_HRY INTEGER not null,
NAZEV VARCHAR2(30) not null,
Date DATE NOT NULL FORMAT 'YYYY-MM-DD',
TIME DATE NOT NULL FORMAT 'HH24: MI: SS',
POPIS CLOB,
constraint PK_GAMES primary key (CIS_HRY)
);
回答by Bob Jarvis - Reinstate Monica
Dates do not have a "format" in Oracle - they always contain the fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. You format dates using the TO_CHARfunction when extracting them for use in a particular situation. I suggest that you re-work your table design as
日期在 Oracle 中没有“格式”——它们总是包含字段 YEAR、MONTH、DAY、HOUR、MINUTE 和 SECOND。TO_CHAR在提取日期以供在特定情况下使用时，您可以使用该函数格式化日期。我建议你重新设计你的桌子设计
create table GAMES (
CIS_HRY INTEGER not null,
NAZEV VARCHAR2(30) not null,
CREATED_DATE_TIME DATE NOT NULL,
POPIS CLOB,
constraint PK_GAMES primary key (CIS_HRY)
);
When fetching data from this table you might use TO_CHAR to format the date:
从此表中获取数据时，您可以使用 TO_CHAR 来格式化日期：
SELECT CIS_HRY,
NAZEV,
TO_CHAR(CREATED_DATE_TIME, 'DD-MON-YYYY HH24:MI:SS') AS CREATE_DT,
FROM GAMES
WHERE NAZEV = 'XYZ';
Share and enjoy.
分享和享受。
回答by David Faber
There are a couple of things you might do in this case. If you're using Oracle 11g or 12c, you could use virtual columns to "store" the date as a VARCHAR2in YYYY-MM-DDformat, and time in your specified format; or you might use a trigger on INSERTor UPDATEto do the same with real columns; or you might (and this is probably the best option), create a view:
在这种情况下，您可以做几件事。如果您使用的是 Oracle 11g 或 12c，则可以使用虚拟列将日期“存储”为VARCHAR2inYYYY-MM-DD格式，并将时间“存储”为您指定的格式；或者您可以在真实列上使用触发器INSERT或UPDATE对真实列执行相同操作；或者你可能（这可能是最好的选择），创建一个视图：
CREATE VIEW games_v AS
SELECT cis_hry, nazev, TO_CHAR(create_dt, 'YYYY-MM-DD') AS chardate
, TO_CHAR(create_dt, 'HH24: MI: SS') AS chartime, popis
FROM games;
If you need to update the "date" and/or "time" in the GAMES_Vview, you can do so via an INSTEAD OFtrigger on GAMES_V. That might be overkill for your purposes.
如果您需要更新的“日期”和/或“时间”GAMES_V视图中，可以通过做这样INSTEAD OF的触发器GAMES_V。就您的目的而言，这可能是矫枉过正。
By the way, I would avoid using dateas a column name in Oracle - it's a reserved word. And you should probably avoid timeas well - I don't think it is a reserved word, but it is generic and not very descriptive.
顺便说一句，我会避免date在 Oracle 中用作列名 - 它是一个保留字。而且您可能也应该避免time- 我不认为它是一个保留词，但它是通用的并且不是很具有描述性。
