从 oracle tmstmp 字段中删除毫秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27659042/
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
removing milliseconds from a oracle tmstmp field
提问by user3022875
I have a TIMESTAMP(6) field in Oracle and I need to remove the millisecond component from the time.
我在 Oracle 中有一个 TIMESTAMP(6) 字段，我需要从时间中删除毫秒组件。
For example I have
例如我有
10/20/2014 10:34:06.356000 AM
and I would like to remove the milliseconds so that I have
我想删除毫秒，以便我有
10/20/2014 10:34:06 AM
Do you know the best way to do this?
你知道这样做的最佳方法吗？
Thank you!
谢谢！
回答by Gordon Linoff
How about this?
这个怎么样？
select cast(col as timestamp(0))
EDIT:
编辑：
The easiest way to avoid rounding is to use trunc()or to subtract half a second:
避免舍入的最简单方法是使用trunc()或减去半秒：
select cast(col - 0.5/(24*60*60) as timestamp(0))
回答by HaveNoDisplayName
try this
尝试这个
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
FROM DUAL;
if you need 12-hour date format
如果您需要 12 小时日期格式
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH:MI:SS AM') "NOW"
FROM DUAL;
回答by MT0
You can either cast it to a timestamp with no fractional seconds (this will round to the nearest second):
您可以将其转换为没有小数秒的时间戳（这将四舍五入到最接近的秒）：
CAST( your_timestamp AS TIMESTAMP(0) )
Or to a DATEdata type (this will truncate to the nearest second):
或DATE数据类型（这将截断到最接近的秒）：
CAST( your_timestamp AS DATE )
If you want it as a TIMESTAMP(0)data type then cast it back:
如果您希望将其作为TIMESTAMP(0)数据类型，则将其转换回：
CAST( CAST( your_timestamp AS DATE ) AS TIMESTAMP(0) )
Or you can convert it to a formatted string and specify the format model you want to use (this will truncate to the nearest second):
或者您可以将其转换为格式化字符串并指定您要使用的格式模型（这将截断到最接近的秒）：
TO_CHAR( your_timestamp, 'YYYY-MM-DD HH24:MI:SS' )
Like this:
像这样：
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE TABLE your_table ( your_timestamp ) AS
SELECT TIMESTAMP '2017-10-25 12:53:12.10076' FROM DUAL;
Query 1:
查询 1：
SELECT CAST( your_timestamp AS TIMESTAMP(0) ) AS "Timestamp",
CAST( your_timestamp AS DATE ) AS "Date",
TO_CHAR( your_timestamp, 'DD-MM-YYYY HH24:MI:SS' ) AS "String"
FROM your_table
结果：
| Timestamp | Date | String |
|-----------------------|----------------------|---------------------|
| 2017-10-25 12:53:12.0 | 2017-10-25T12:53:12Z | 25-10-2017 12:53:12 |
note: How the TIMESTAMPand DATEare formatted in the output will depend on your NLS_TIMESTAMP_FORMATand NLS_DATE_FORMATsession parameters but you can directly control the formatting of TO_CHARwhen you specify a format model.
注意：TIMESTAMP和DATE在输出中的格式取决于您NLS_TIMESTAMP_FORMAT和NLS_DATE_FORMAT会话参数，但您可以直接控制TO_CHAR指定格式模型时的格式。
回答by Sandeep
This might help!
这可能会有所帮助！
select substr(to_char('10/20/2014 10:34:06.356000 AM'),1,instr(to_char('10/20/2014 10:34:06.356000 AM'),'.')-1)||' '||
substr(to_char('10/20/2014 10:34:06.356000 AM'),-2,instr(to_char('10/20/2014 10:34:06.356000 AM'),'.')-1) "Date"
from dual;
