to_date returns wrong year

Question

I need help regarding date output. I am new to oracle, mostly of my time I am using msSQL.

I have a date and time stored as varchar and I need output so that I can import it to sql db.

select to_date('210408092508', 'yy/mm/dd HH24:MI:SS') from dual; returns 08.04.2021 9:25:08 AM which is ok

select to_date('210408091706', 'yy/mm/dd HH24:MI:SS') from dual; returns 09.08.2104 5:06:00 PM. The output should be 08.04.2021 9:17:06 AM

As you can see the year and time in second output is wrong... If I set HH12, its working well, but it must be HH24, because of other data in the table.

I want to know why is this happening when I explicitly set the format of the string and then what can I do to solve this?

Thanks

I assume your problem comes from the format string being incorrect. Try `select to_date('210408091706', 'yymmddHH24MISS') from dual;`. I assume it tries to apply the formatting and can't so makes a best effort based on what it sees. You should make a formatting string be of the same format of your input https://www.techonthenet.com/oracle/functions/to_date.php — Hive7, Apr 19 '21 at 10:37

Alex Poole · Accepted Answer · 2021-04-19T11:19:37.143

As @Hive7 said, Oracle is 'helpfully' making a best-guess on how to match your string against the format model. As it says in the documentation:

Oracle Database converts strings to dates with some flexibility.

You can tell it not to do that by adding the FX format modifier, which will both make of your conversions fail with "ORA-01861: literal does not match format string", because they don't.

You're getting different results because of the flexibility and how Oracle tries to work out (or guess) which bits of your string mean what. The first string is being interpreted as YYMMDDHH24MISS, while the second string is being interpreted as YYYYMMDDHH24MI.

210408092508
YYMMDDHHMISS => 2021-04-08 09:25:08
      24

210408091706
YYYYMMDDHHMI => 2021-08-09 17:06:00
        24

And that difference is because 17 is a valid number for the hour element, but 25 is not.

210408092508
YYYYMMDDHHMI => ORA-01850: hour must be between 0 and 23
        24

It seems to prefer assuming a 4-digit year, which isn't unreasonable, even if that effectively means the seconds are ignored. But in the first string, when it sees the 25 it discards that possible full format model, and somewhere further down the list is one that has a 2-digit year - which shifts how the month, day, hour, minute and second are interpreted.

If I set HH12, its working well

It's working more consistently for these sample values; but still not well. If you do that than 17 is also now no longer valid (ORA-01849: hour must be between 1 and 12), so that format is discarded again, and it settles on the same 2-digit-year version as it does for hour 25. But if you try that with an hour value between 1 and 12 you'll get the wrong result again. And higher minute/second values will also give different results.

As @Hive7 also said, the correct way to handle this is to use the proper matching format model:

select to_date('210408092508', 'RRMMDDHH24MISS') from dual;

2021-04-08 09:25:08

select to_date('210408091706', 'RRMMDDHH24MISS') from dual;

2021-04-08 09:17:06

I've switched from YY to RR because that's generally preferable if you really have to deal with 2-digit year values, but you might have a reason to use YY. The result is the same here.

db<>fiddle demo

Thanks Alex this did the trick And yes, unfortunately I have to work with 2 digit years... — sosNiLa, Apr 19 '21 at 11:12

Boneist · Answer 2 · 2021-04-19T11:03:01.593

Your format mask doesn't match your data. Also, your data is using two digit years which is... ugh; Y2K really was a thing - no-one should be using 2 digit years 20 years later!

Here's a demo using your format mask, along with two other - better - format masks:

WITH your_data AS (SELECT '210408092508' date_str FROM dual UNION ALL
                   SELECT '210408091706' date_str FROM dual UNION ALL
                   SELECT '510408091706' date_str FROM dual)
SELECT date_str,
       to_date(date_str, 'yy/mm/dd HH24:MI:SS') your_dt,
       to_date(date_str, 'yymmddhh24miss') dt1,
       to_date(date_str, 'rrmmddhh24miss') dt2
FROM   your_data;


DATE_STR     YOUR_DT             DT1                 DT2
------------ ------------------- ------------------- -------------------
210408092508 08/04/2021 09:25:08 08/04/2021 09:25:08 08/04/2021 09:25:08
210408091706 09/08/2104 17:06:00 08/04/2021 09:17:06 08/04/2021 09:17:06
510408091706 09/08/5104 17:06:00 08/04/2051 09:17:06 08/04/1951 09:17:06

where you can see how Oracle's guesses to how to read the string that's passed in with a different format to the one it's been told to expect is very different than if you give it the right information to work with!

I've included two possible format masks to use depending on how you want the date handling to be - YY will guess the first two digits of the year should be that of the current year, whereas RR will guess the first two digits of the year to be that of the current year if the year is 00-49, otherwise it chooses the first two digits of the year a hundred years ago.

However, if you have any say over the data, make it use four-digit years, and then Oracle doesn't have to guess at all, because you can use YYYY in your format mask instead.

Thanks for a very nice explanation, both you and Alex. Unfortunately, I have to work with 2 digit years because the data in the table are very old, about 30 years old... — sosNiLa, Apr 19 '21 at 11:14
then I would recommend using `rr` not `yy` as the format mask for your two digit years, assuming they're historical dates (e.g. dates of birth) — Boneist, Apr 19 '21 at 11:57
"Y2K really was a thing - no-one should be using 2 digit years 20 years later" - be merciful, when Y2K was on everyone's lips the developers from today were still kids. But of course, you are absolutely right. — Wernfried Domscheit, Apr 19 '21 at 12:29
@WernfriedDomscheit Y2K was at the start of my career, back when I was a system tester, and I saw how hard the people in my team worked to ensure it was the non-event it ended up being! It's also why I'm so militant about people getting dates correct; `to_date` isn't that hard to learn! — Boneist, Apr 19 '21 at 13:12
_" when Y2K was on everyone's lips the developers from today were still kids"_ True enough. I see this as a failure to pass on - or failure to absorb - 'lessons learned'. — EdStevens, Apr 19 '21 at 14:13

to_date returns wrong year

2 Answers2