将日期时间字段转换为 SQL (Oracle) 中的日期字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26062132/
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
Convert datetime field to just a date field in SQL (Oracle)
提问by tia97
I've seen a few answers to questions similar to mine but I cannot get them to work. I have several date fields in my query that return the date and time like such 7/1/2014 12:00:00 AM. Is there a way I can just have the fields show 7/1/2014?
我已经看到了一些与我类似的问题的答案，但我无法让它们发挥作用。我的查询中有几个日期字段返回日期和时间，例如 7/1/2014 12:00:00 AM。有没有办法让字段显示 7/1/2014？
SELECT DISTINCT
C.RECEIPTDATE,
(I.CLIENTID ||' - '||PO.CLIENTNAME) AS CLIENT,
D.INVOICEID,
D.SVCFROMDATE,
D.SVCTODATE,
D.SVCCODE
FROM M_EQP_ORDERS
WHERE.....
I basically would like to cut down the two date fields to the shorter date format minus the time.
我基本上想将两个日期字段减少到较短的日期格式减去时间。
Thanks in advance!
提前致谢！
回答by Aramillo
Use to_char function:
使用 to_char 函数：
SELECT DISTINCT
to_char(C.RECEIPTDATE,'DD/MM/YYYY'),
(I.CLIENTID ||' - '||PO.CLIENTNAME) AS CLIENT,
D.INVOICEID,
D.SVCFROMDATE,
D.SVCTODATE,
D.SVCCODE
FROM M_EQP_ORDERS
WHERE.....
回答by Vulcronos
Just use the function TRUNC.
只需使用功能 TRUNC。
SELECT DISTINCT
TRUNC(C.RECEIPTDATE),
(I.CLIENTID ||' - '||PO.CLIENTNAME) AS CLIENT,
D.INVOICEID,
TRUNC(D.SVCFROMDATE),
TRUNC(D.SVCTODATE),
D.SVCCODE
FROM M_EQP_ORDERS
WHERE.....
回答by Lalit Kumar B
DEPENDSon the data type.
取决于数据类型。
If the column is DATEdata type, then, as suggested already, TRUNCwould do the job to display. But, if your locale-specific NLS date settingsare different, then you will still see the time portion as midnight.
如果列是DATE数据类型，那么正如已经建议的那样，TRUNC将完成显示工作。但是，如果您的特定于区域设置的 NLS 日期设置不同，那么您仍然会看到时间部分为午夜。
Else, you need to use TO_DATEwith proper FORMATand apply TRUNCto it.
否则，您需要TO_DATE正确使用FORMAT并应用TRUNC它。
update
更新
If you only want to display, use TO_CHAR, else, if you have a filter in your WHEREclause, then remember TO_CHARdoesn't return DATE, it converts it into literal.
如果您只想显示，使用TO_CHAR，否则，如果您的WHERE子句中有过滤器，那么请记住TO_CHAR不会返回DATE，它会将其转换为文字。
回答by neshkeev
Try this:
尝试这个：
SQL> select to_char(sysdate, 'YYYY/MM/DD') dateonly, sysdate datetime from dual;
DATEONLY DATETIME
---------- -------------------
2014/09/26 2014-09-26 15:41:03
回答by Bacs
The Oracle date datatype always includes the time.
Oracle 日期数据类型始终包括时间。
TRUNC will truncate the time to midnight, which you will need to do if you want to match the date parts of two datetimes. The time may still display, depending on how your client is configured, so use TO_CHAR with an appropriate format mask to display it whatever way you want.
TRUNC 会将时间截断到午夜，如果要匹配两个日期时间的日期部分，则需要这样做。时间可能仍会显示，具体取决于您的客户端的配置方式，因此请使用带有适当格式掩码的 TO_CHAR 以您想要的任何方式显示它。
