Wrong conversion of date-string to date

Question

When running these statements:

select to_date('201103270100', 'YYYYMMDDHH24MI') from dual;
select to_date('201103270130', 'YYYYMMDDHH24MI') from dual;

I get these results:

27/3/2011 1:00:00
27/3/2011 1:30:00

which are correct.

But when running:

select to_date('201103270200', 'YYYYMMDDHH24MI') from dual

I get a wrong hour as result

27/3/2011 3:00:00

More samples:

select to_date('201103270215', 'YYYYMMDDHH24MI') from dual
select to_date('201103270245', 'YYYYMMDDHH24MI') from dual
select to_date('201103270300', 'YYYYMMDDHH24MI') from dual
select to_date('201103270330', 'YYYYMMDDHH24MI') from dual

27/3/2011 3:15:00
27/3/2011 3:45:00
27/3/2011 3:00:00
27/3/2011 3:30:00

When the date is 20110326, I get the correct result.

This is crazy... Is Oracle SQL bullying me with this conversion? Help is welcome!

It works right for me. See sqlfiddle http://sqlfiddle.com/#!4/47e5d/3 — Guneli, Apr 21 '15 at 13:35
@alfasin, thought daylight saving did not start until 29th March 2015 ;) — PaulFrancis, Apr 21 '15 at 13:36
@PaulFrancis This is 2011 we're talking about. It was the 27th there ;) — Siguza, Apr 21 '15 at 13:42
@Siguza Depends on where you live in the world. For the US I think it was the 16th (at least that's what google told me). But yes, Daylight savings would be my guess as well. — waka, Apr 21 '15 at 13:49
SELECT dbtimezone FROM DUAL; to find out what your system's current timezone, which affects the DST rules, is. — David Aldridge, Apr 21 '15 at 13:50
In Europe, in the year 2011, the day light saving was with effect from 27th March, 2011 at 2:00 AM. — Lalit Kumar B, Apr 21 '15 at 13:56
@DavidAldridge, that's wrong. `DBTIMEZONE` only defines the "internal format" of `TIMESTAMP WITH LOCAL TIMEZONE` values. Use `Select SESSIONTIMEZONE From dual;` or get timezone of database server via `select TZ_OFFSET(SYSTIMESTAMP) from dual` — Wernfried Domscheit, Apr 21 '15 at 19:52

score 2 · Accepted Answer · answered Apr 21 '15 at 13:54

Is Oracle SQL bullying me with this conversion?

Oracle is too dumb to bully a developer, unless the developer tries to bully it ;-)

There can't be different outputs without any reason. In Europe, in the year 2011, the day light saving was with effect from 27th March, 2011 at 2:00 AM. Source is wikipedia.

So, keeping in mind the day light saving, let's see the results for the datetime after 2:00 AM:

SQL> SELECT to_char(to_date('201103270100', 'YYYYMMDDHH24MI'), 'DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('20
-------------------
27/03/2011 01:00:00

SQL> SELECT to_char(to_date('201103270130', 'YYYYMMDDHH24MI'), 'DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('20
-------------------
27/03/2011 01:30:00

SQL> SELECT to_char(to_date('201103270200', 'YYYYMMDDHH24MI'), 'DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('20
-------------------
27/03/2011 02:00:00

SQL> SELECT to_char(to_date('201103270215', 'YYYYMMDDHH24MI'), 'DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('20
-------------------
27/03/2011 02:15:00

SQL> SELECT to_char(to_date('201103270245', 'YYYYMMDDHH24MI'), 'DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('20
-------------------
27/03/2011 02:45:00

SQL> SELECT to_char(to_date('201103270300', 'YYYYMMDDHH24MI'), 'DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('20
-------------------
27/03/2011 03:00:00

SQL> SELECT to_char(to_date('201103270330', 'YYYYMMDDHH24MI'), 'DD/MM/YYYY HH24:MI:SS') FROM dual;

TO_CHAR(TO_DATE('20
-------------------
27/03/2011 03:30:00

SQL>

If your provided data and examples are correct, then this should be the reason.

So, you should see the time portion exactly at 2:00 AM to shift to 3:00 AM. Similarly, 2:15 AM would be 3:15 AM. Just add an hour to the time for those which are after 2:00 AM.

Ugh - I've dug myself to deep into this problem to see clear. Thanks! Strange that our source system accepts this date/time... But that's another problem. — davidbe, Apr 22 '15 at 07:04

Wrong conversion of date-string to date

1 Answers1