I have a field (LPSDT) that is a date and time in a string format: 20170312112507
In my query I substring the time as substr(LPSDT, 9, 2)||':'||substr(LPSDT, 11, 2)||':'||substr(LPSDT, 13, 2) which results in 11:25:07 but it is a string not an actual time format.
If I use the following: time(substr(lpsdt, 9, 6) I get 3/1/2017 11:25:07 AM as a result which makes no sense since I am substringing the data that only equates to the time and the date it is returning is wrong. The date does not match what is actually in the field. The system seems to be making assumptions about the data rather than simply returning the expected value.
I've tried to_date with a format of 'hh24miss' and get the same result.