Oracle timestamp string value conversion-CodePudding

I'm Trying to fetch values between two timestamps, however the conversion timestamp failing with formatting error.

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')

CodePudding user response：

You put the 2 arguments of TO_TIMESTAMP in only 1 string. Note also that your date format is NLS dependent.

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')

CodePudding user response：

Use a TIMESTAMP literal:

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';