oracle PL SQL - 将时间戳转换为日期时间/日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44978821/
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
PL SQL - Convert timestamp to datetime/date
提问by Anna Huang
select
to_timestamp(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF') as SCHEDULED_TIME,
TRUNC(to_date(to_timestamp(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS'))
from S_TIDAL_STATUS
The error was: ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"
错误是：ORA-01830：日期格式图片在转换整个输入字符串之前结束 01830. 00000 - “日期格式图片在转换整个输入字符串之前结束”
The goal is to return something like
目标是返回类似的东西
2017-07-91 23:14:00 (without the content after the dot).
2017-07-91 23:14:00（点后没有内容）。
Here's what the SCHEDULED_TIME (timestamp) looked like:
这是 SCHEDULED_TIME（时间戳）的样子：
回答by mathguy
The problem in your attempt is the function TO_DATE() applied to a timestamp. TO_DATE() takes a VARCHAR2 (string) input, not a timestamp. So Oracle converts the timestamp to a string first, implicitly, using your NLS_TIMESTAMP_FORMAT parameter, and then attempts to convert this string to a date. Depending on your NLS_TIMESTAMP_FORMAT, you may get different errors.
您尝试中的问题是应用于时间戳的函数 TO_DATE()。TO_DATE() 采用 VARCHAR2（字符串）输入，而不是时间戳。因此，Oracle 首先使用 NLS_TIMESTAMP_FORMAT 参数隐式地将时间戳转换为字符串，然后尝试将此字符串转换为日期。根据您的 NLS_TIMESTAMP_FORMAT，您可能会收到不同的错误。
The way to convert a timestamp to a date (datetime) - while truncating off the fractions of a second - is with the CAST function. Example:
将时间戳转换为日期 (datetime) 的方法 - 同时截断秒的分数 - 是使用 CAST 函数。例子：
select systimestamp,
cast (systimestamp as date) as ts_cast_to_date
from dual
;
Alternatively, if all your strings are EXACTLY in that format, you can truncate the strings first and apply TO_DATE directly:
或者，如果您的所有字符串都完全采用该格式，您可以先截断字符串并直接应用 TO_DATE：
to_date(substr(scheduled_time, 1, 19), 'yyyy-mm-dd hh24:mi:ss')
回答by Jeff Holt
This should do the trick:
这应该可以解决问题：
select
to_char(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF') as time_to_csecs,
to_char(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS') as time_to_secs,
TRUNC(to_date(to_char(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) as time_to_day
from S_TIDAL_STATUS
Please review the docs to see the difference between to_timestamp and to_char.
请查看文档以了解 to_timestamp 和 to_char 之间的区别。
