Hi AT-Team,
LiveSQL seems to be broken, so sorry no test case there.
We are building a system in which developers are converting strings holding time-only data to a DATE in Oracle SQL in order to build queries involving the time of day later on. Simple enough: use TO_DATE(). However I am wondering about the rules about the date part of the resulting datetime value.
E.g.
(...)
to_date('13:00:00', 'hh24:mi:ss')
(...)This works fine.
But when I select the resulting value back:
select to_char(to_date('13:00:00', 'hh24:mi:ss'), 'yyyy-mm-dd"T"hh24:mi:ss')
from dual;results in:
TO_CHAR(TO_DATE('13
-------------------
2018-01-01T13:00:00So here we see the date value of '2018-01-01' is added to the time value by the to_date() call. My question is where this behavior is documented? I would like to come with some advise to the developers how to handle this use case and all I have right now is the assumption that the fist day of the current year of the database servers system date is taken but I cannot confirm this in documentation at all.
Any thoughts?
Regards and Thanks,
Henk
If this is in the documentation, I can't find it ;)
But MOS note 1014079.6 discusses this. It gives the following rules
- Day missing = return 1st of the month
- Month missing = return current month
Also the following rule is true to my knowledge:
- Year missing = return current year
select to_char(sysdate, 'DD MON YYYY') today,
to_date('FEB 2016', 'MON YYYY') no_day,
to_date('22 2017', 'DD YYYY') no_month,
to_date('22 MAR', 'DD MON') no_year
from dual;
TODAY NO_DAY NO_MONTH NO_YEAR
25 JAN 2018 01-FEB-2016 00:00:00 22-JAN-2017 00:00:00 22-MAR-2018 00:00:00