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
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.