如何将“2016-07-01 01:12:22 PM"转换为“2016-07-01 13:12:22"小时格式?

2021-09-11 00:00:00 timestamp sql type-conversion oracle

谁能帮助我如何在 PL/SQL 中将 2016-07-01 01:12:22 PM 转换为 2016-07-01 13:12:22?我使用了以下但没有运气.

Can anyone help me how to convert 2016-07-01 01:12:22 PM to 2016-07-01 13:12:22 in PL/SQL? I used the following but no luck.

SELECT TO_TIMESTAMP ('08-FEB-19 06.41.41.000000 PM', 'DD-Mon-RR HH24:MI:SS.FF') 
FROM dual

我预计:08-FEB-19 18.41.41.000000

I expect: 08-FEB-19 18.41.41.000000

我收到以下错误:

ORA-01830:日期格式图片在转换整个输入字符串之前结束

ORA-01830: date format picture ends before converting the entire input string

推荐答案

您当前的时间戳没有任何意义，因为它指定的时间为 18 小时，即下午 6 点，但随后它也指定了AM 子午线指示器，表示早于中午.因此，您可以从 TO_TIMESTAMP 模式中删除 AM:

Your current timestamp makes no sense, because it specifies the time as 18 hours, which is 6pm, but then it also specifies the AM meridian indicator, which means earlier than noon. So, you may remove the AM from your TO_TIMESTAMP pattern:

SELECT TO_TIMESTAMP ('08-FEB-19 18.41.41.000000', 'DD-Mon-RR HH24.MI.SS.FF')
FROM dual;

08-FEB-19 06.41.41.000000000 PM

请注意，Oracle 内部确实没有 12 或 24 小时格式时间戳之类的东西.相反，如果您想以 24 小时格式查看您的 Oracle 时间戳，您可以使用适当的 24 小时格式掩码调用 TO_CHAR 之类的操作:

Note that there is really no such thing as 12 or 24 hour format timestamp internally in Oracle. Rather, if you want to view your Oracle timestamp in 24 hour format, you may do something like call TO_CHAR with an appropriate 24 hour format mask:

SELECT
    TO_CHAR(TO_TIMESTAMP ('08-FEB-19 18.41.41.000000', 'DD-Mon-RR HH24.MI.SS.FF'),
        'DD-Mon-RR HH24.MI.SS.FF') AS ts
FROM dual;

08-Feb-19 18.41.41.000000000

演示

如果您想转换带有 12 小时时间和 AM/PM 组件的时间戳字符串，我们可以尝试:

If you wanted to convert a timestamp string with 12 hour time and an AM/PM component, we can try:

SELECT
    TO_CHAR(
        TO_TIMESTAMP ('08-FEB-19 06.41.41.000000 PM', 'DD-Mon-RR HH.MI.SS.FF PM'),
        'DD-Mon-RR HH24.MI.SS.FF')
FROM dual;

相关文章