以毫秒为单位的 Oracle 中的日期字符串

2021-12-06 00:00:00 oracle to-date

我想将以下字符串转换为日期:

I want to convert the follow string to date:

2004-09-30 23:53:48,140000000

我试过了:

to_date('#', 'YYYY-MM-DD HH24:MI:SS,FF9')

但是 PL/SQL 不断抛出这个错误:

But PL/SQL keep throwing this error:

ORA-01821: date format not recognized.

FF9 对 Oracle 不正确，有什么建议吗?

FF9 is incorrect for Oracle, any suggestion?

推荐答案

Oracle 仅在 DATE 字段中存储最高达秒的分数.

Oracle stores only the fractions up to second in a DATE field.

改用TIMESTAMP:

SELECT  TO_TIMESTAMP('2004-09-30 23:53:48,140000000', 'YYYY-MM-DD HH24:MI:SS,FF9')
FROM    dual

，可能将其转换为 DATE 然后:

, possibly casting it to a DATE then:

SELECT  CAST(TO_TIMESTAMP('2004-09-30 23:53:48,140000000', 'YYYY-MM-DD HH24:MI:SS,FF9') AS DATE)
FROM    dual

相关文章