Oracle timestamp string value conversion

Question

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

p3consulting · Accepted Answer · 2022-11-17 17:41:08Z

1

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

edited Nov 17, 2022 at 17:41

answered Nov 17, 2022 at 17:29

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2022-11-17 23:10:20Z

0

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

answered Nov 17, 2022 at 23:10

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Oracle timestamp string value conversion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related