2

Let's create a test table to illustrate the problem:

CREATE TABLE "TMP1" (
    "X" VARCHAR2(256 BYTE) NOT NULL
) LOGGING NOCOMPRESS NOCACHE;

INSERT INTO "TMP1" VALUES ('XxYyyyZzzWww_123456_0106201395810_0106201395810.csv');
INSERT INTO "TMP1" VALUES ('XxYyyyZzzWww_123456_31052013155754_31052013155754.csv');
INSERT INTO "TMP1" VALUES ('XxYyyyZzzWww_123456_02062013130000_02062013130000.csv');

Now make a request:

select
        TMP2.X
    , TMP2.STARTDATETIME
    , to_char(TMP2.STARTDATETIME, 'DDMMYYYYHH24MISS') "to_char(StartDateTime)"
    , TMP2.ENDDATETIME
    , to_char(TMP2.ENDDATETIME, 'DDMMYYYYHH24MISS') "to_char(EndDateTime)"
from (
        select
                "X"
            , to_date(case length(regexp_substr("X", '\d+', 1, 2, 'i'))
                            when 14 then regexp_substr("X", '\d+', 1, 2, 'i')
                            when 13 then substr(regexp_substr("X", '\d+', 1, 2, 'i'), 0, 8) 
                                || '0' || substr(regexp_substr("X", '\d+', 1, 2, 'i'), 9)
                end, 'DDMMYYYYHH24MISS') StartDateTime

            , to_date(case length(regexp_substr("X", '\d+', 1, 3, 'i'))
                            when 14 then regexp_substr("X", '\d+', 1, 3, 'i')
                            when 13 then substr(regexp_substr("X", '\d+', 1, 3, 'i'), 0, 8) 
                                || '0' || substr(regexp_substr("X", '\d+', 1, 3, 'i'), 9)
                end, 'DDMMYYYYHH24MISS') EndDateTime
        from
            "TMP1"
) TMP2;

In this query, we cut out a substring and convert it to a date. Then we try to do the reverse conversion, but does not work.

Result:

enter image description here

to_date function is working, but the function to_char not.

abg
  • 2,002
  • 7
  • 39
  • 63

1 Answers1

2

Your sample works fine.

I'd suspect an issue with Your Oracle DB instance - there's been a couple of bugs with inner SQL-rewriting/optimization, where Oracle ended up executing wrongfully-rewritten queries. I'd try turning rewrites off (look for QUERY REWRITE ENABLED, EXPLAIN_REWRITE, EXPLAIN_PLAN).

Sometimes a restart of DB helped.

Vlad
  • 1,157
  • 8
  • 15