Oracle时间戳字符串值转换

Question

我正在尝试获取两个时间戳之间的值，但是转换时间戳失败，格式错误。

SELECT
*
FROM
PKV
WHERE
extended_timestamp BETWEEN TO_TIMESTAMP('28-OCT-22 01.10.37.153016000 PM ASIA/CALCUTTA,DD-MON-YY HH24:MI:SS') AND TO_TIMESTAMP(
'28-OCT-22 10.10.37.153016000 PM ASIA/CALCUTTA,DD-MON-YY HH24:MI:SS')

Answer 1

将TO_TIMESTAMP的两个参数仅放在一个字符串中。还要注意，您的日期格式取决于NLS。

TO_TIMESTAMP_TZ('28-OCT-22 01.10.37.153016000 PM ASIA/CALCUTTA','DD-MON-YY HH12:MI:SS.FF9 PM TZR', 'NLS_DATE_LANGUAGE = American') 

Answer 2

使用TIMESTAMP文字:

SELECT *
FROM   PKV
WHERE  extended_timestamp
BETWEEN TIMESTAMP '2022-10-28 13:10:37.153016000 ASIA/CALCUTTA'
AND     TIMESTAMP '2022-10-28 22:10:37.153016000 ASIA/CALCUTTA';