SQL 在 Oracle 中只获取没有时间的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44105462/
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
Get only date without time in Oracle
提问by JDoeBloke
I have this sql request to retrieve some data and I have this columd 'pa.fromdate' which returns a date and a time. How do I get it to return only date in 'DD.MM.YYYY' format. I've tried something like trunc(to_date(pa.fromdate, 'MM.DD.YYYY')). Doesn't work. How can I do that?
我有这个 sql 请求来检索一些数据，我有这个列“pa.fromdate”，它返回一个日期和时间。如何让它仅以“DD.MM.YYYY”格式返回日期。我试过类似的东西trunc(to_date(pa.fromdate, 'MM.DD.YYYY'))。不起作用。我怎样才能做到这一点？
SELECT pro.inscatid,
t.epotypeid,
t.paysum,
t.note,
pa.blankno,
pa.blankseria,
pa.signtime,
pa.fromdate,
ca.accnum,
ou.name,
cst.inn,
pkg_customers.GETCUSTOMERFULLNAME(cst.id) cstfio
FROM epo_orders t
LEFT JOIN epo_orderdetails od
ON od.orderid = t.id
AND od.iscanceldoc = -1
LEFT JOIN plc_Agree pa
ON pa.id = od.agreeid
LEFT JOIN pro_products pro
ON pro.id = pa.proid
LEFT JOIN nsk_transferdetails td
ON td.transferid = pa.transferid
AND td.orgtypeid = 4
LEFT JOIN cst_customers cst
ON cst.id = t.cstid
LEFT JOIN cst_cstaccounts ca
ON ca.id = t.cstaccid
LEFT JOIN nsk_orgunits ou
ON td.orgunitid = ou.id
WHERE t.epotypeid IN (159,1010,169,175)
AND rownum <20;
回答by Thorsten Kettner
Usually one would simply truncate the datetime with TRUNC:
通常一个人会简单地截断日期时间TRUNC：
TRUNC(pa.fromdate)
This removes the time part from the datetime, so you get the mere date. Then in your application layer, you would care about how to display it.
这会从日期时间中删除时间部分，因此您只会得到日期。然后在你的应用层，你会关心如何显示它。
For example you have a GUI with a grid. The grid displays the dates according to the user's system settings (e.g. Windows region settings), but the grid knows it's dates and can sort accordingly. For this to happen, you'd fill the grid with dates, not with stringsrepresenting a date.
例如，您有一个带有网格的 GUI。网格根据用户的系统设置（例如 Windows 区域设置）显示日期，但网格知道它的日期并可以相应地排序。要做到这一点，您需要用date填充网格，而不是用表示日期的字符串。
If you want a fixed string format (e.g. in order to write into a file), you can use TO_CHARinstead:
如果你想要一个固定的字符串格式（例如为了写入文件），你可以使用TO_CHAR：
TO_CHAR(pa.fromdate, 'dd.mm.yyyy')
回答by Akanksha
Try to use to_char(pa.fromdate, 'MM.DD.YYYY'), and this will give you desired result
尝试使用to_char(pa.fromdate, 'MM.DD.YYYY')，这会给你想要的结果
回答by Vecchiasignora
don't use to_date()on date value column, use to_char()with mask, like this
不要to_date()在日期值列上使用，to_char()与掩码一起使用，像这样
select
......
to_char(pa.fromdate,'MM.DD.YYYY') fromdate
......
from ....
where ....
回答by Ersin Gülbahar
try this
尝试这个
select pro.inscatid,
t.epotypeid,
t.paysum,
t.note,
pa.blankno,
pa.blankseria,
pa.signtime,
to_char(pa.fromdate,'MM.DD.YYYY')fromdate,
ca.accnum,
ou.name,
cst.inn,
pkg_customers.GETCUSTOMERFULLNAME(cst.id) cstfio
from
epo_orders t
left join epo_orderdetails od on od.orderid = t.id and od.iscanceldoc = -1
left join plc_Agree pa on pa.id = od.agreeid
left join pro_products pro on pro.id = pa.proid
left join nsk_transferdetails td on td.transferid = pa.transferid and td.orgtypeid = 4
left join cst_customers cst on cst.id = t.cstid
left join cst_cstaccounts ca on ca.id = t.cstaccid
left join nsk_orgunits ou on td.orgunitid = ou.id
where t.epotypeid in (159,1010,169,175) and rownum<20
回答by Shine
You can make use of to_char()function for this. And you can use trunc()to solve your issue.
您可以to_char()为此使用功能。你可以trunc()用来解决你的问题。
回答by Dean Newstead
SELECT to_char(Column_name,'DD/MM/YYYY') Column_Name FROM Table_Name
SELECT to_char(Column_name,'DD/MM/YYYY') Column_Name FROM Table_Name
