Why is my timestamp getting truncated when I select the maximum, non-null value from a series of columns?

So, I have a series of columns and I need the maximum, non-null value.

Here is a sample of the problem, and it works as I would expect:

select t.*, (select  max(to_timestamp(column_value)) as END_TIME from sys.dbms_debug_vc2coll(PROCESSEND,COMPLETEDTIME,processstart) where COLUMN_VALUE is not null) as END_TIME
from (
    select 1 as id, TIMESTAMP '2024-06-20 18:07:50' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:48' as PROCESSEND, null as COMPLETEDTIME from dual
    union all
    select 2 as id, TIMESTAMP '2024-06-20 18:05:37' as PROCESSSTART, TIMESTAMP '2024-06-20 18:05:19' as PROCESSEND, TIMESTAMP '2024-06-20 18:05:21' as COMPLETEDTIME from dual
    union all
    select 3 as id, TIMESTAMP '2024-06-20 18:07:43' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:39' as PROCESSEND, TIMESTAMP '2024-06-20 18:07:40' as COMPLETEDTIME from dual
    union all
    select 4 as id, TIMESTAMP '2024-06-20 18:07:47' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:45' as PROCESSEND, TIMESTAMP '2024-06-20 18:07:46' as COMPLETEDTIME from dual
    union all
    select 5 as id, TIMESTAMP '2024-06-20 18:07:32' as PROCESSSTART, null as PROCESSEND, TIMESTAMP '2024-06-20 18:07:24' as COMPLETEDTIME from dual
    union all
    select 6 as id, TIMESTAMP '2024-06-20 18:07:32' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:20' as PROCESSEND, TIMESTAMP '2024-06-20 18:07:24' as COMPLETEDTIME from dual
    union all
    select 7 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, null as COMPLETEDTIME from dual
    union all
    select 8 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, TIMESTAMP '2024-06-20 18:04:40' as COMPLETEDTIME from dual
    union all
    select 9 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, TIMESTAMP '2024-06-20 18:04:40' as COMPLETEDTIME from dual
    union all
    select 10 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, TIMESTAMP '2024-06-20 18:04:40' as COMPLETEDTIME from dual
) t;

And, results as as expected:

However, when I run this exact same solution against the actual data, I get truncated dates:

The data types for these 3 columns in the DB are:

 PROCESSSTART                                       DATE
 PROCESSEND                                         DATE
 COMPLETEDTIME                                      DATE

The string output I get for column_value is of the form: 20-JUN-24 06.07.50.000000000 PM

I have run

select  max(to_timestamp(column_value)) as END_TIME 
from sys.dbms_debug_vc2coll(PROCESSEND,COMPLETEDTIME,processstart

with to_date and to_date(regexp_replace(COLUMN_VALUE,'\.0{1,9} (AM|PM)',''),'DD-MON-YY hh.mi.ss AM') and I still get a truncated date.

Anyone know what's going on?

You are relying on implicit conversions and NLS settings. Although your client/application is showing you the full date and timestamp values (so apparently that is not itself using NLS settings) your query is producing the wrong result, and your client then just shows that.

You have two implicit conversions. dbms_debug_vc2coll is a collection of varchar2, so when you populate that with date values from your real table, those dates are being implicitly converted to strings. Based on your timestamp format, which from what you have said appears to be 'DD-MON-RR HH24:MI:SS:FF AM', you probably have NLS_DATE_FORMAT set to the sort-of-default 'DD-MON-RR'.

That means your query is really more like:

select t.*,
  (
    select max(to_timestamp(column_value, 'DD-MON-RR HH:MI:SS.FF AM')) as END_TIME
    from sys.dbms_debug_vc2coll(
      to_char(PROCESSEND, 'DD-MON-RR'),
      to_char(COMPLETEDTIME, 'DD-MON-RR'),
      to_char(processstart, 'DD-MON-RR')
    )
    where COLUMN_VALUE is not null
  ) as END_TIME
from t;

The string versions of the dates have no time - the strings are like '20-JUN-24', so for your first row your collection is ['20-JUN-24','20-JUN-24',null] - so when those strings are converted back to timestamps there is no useful time information. That's where the truncation happens. You convert the values back to timestamp, using the NLS settings again, but from the string that has no time data - to_timestamp('20-JUN-24', 'DD-MON-RR HH:MI:SS.FF AM') comes out as 2024-06-20 00:00:00.0.

You can avoid the problem by changing your session's NLS settings to formats that would sort as you want:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF3';

or, preferably and more safely, supply the formats explicitly in the query:

select t.*,
  (
    select max(to_timestamp(column_value, 'YYYY-MM-DD HH24:MI:SS')) as END_TIME
    from sys.dbms_debug_vc2coll(
      to_char(PROCESSEND, 'YYYY-MM-DD HH24:MI:SS'),
      to_char(COMPLETEDTIME, 'YYYY-MM-DD HH24:MI:SS'),
      to_char(processstart, 'YYYY-MM-DD HH24:MI:SS')
    )
    where COLUMN_VALUE is not null
  ) as END_TIME
from t;

(You also don't need the null filter as max ignores nulls.)

Either way, now the collection is ['2024-06-20 18:07:50','2024-06-20 18:07:48', null], and converting back to_timestamp('2024-06-20 18:07:50', 'YYYY-MM-DD HH24:MI:SS') evaluates as 2024-06-20 18:07:50.0, with the time set as you expect.

fiddle

It would be better to compare the values as dates, rather than converting to and from strings at all.

Unfortunately you can't just do greatest(processend, completedtime, processstart) because that will evaluate to null if any of the values is null. You could use nvl or coalesce to substitute a magic value what you know will never actually, appear; for eaxmple:

select t.*,
  greatest(
    coalesce(processend, date '0001-01-01'),
    coalesce(completedtime, date '0001-01-01'),
    coalesce(processstart, date '0001-01-01')
  ) as end_time
from t;

fiddle

Magic values aren't ideal either, but sometimes are the lesser evil, and in this case it looks unlikely you'll have real data much in the past.

And if all three columns can be null - also maybe unlikely but certainly possible - then you'd need to revert the magic value back to null, as @Thorsten showed.

If you only wanted the end_time for each ID you could also do a simple unpivot:

select id, max(value) as end_time
from t
unpivot (value for flag in (processstart as 'A', processend as 'B', completedtime as 'C'))
group by id;

If you want all the columns it's slightly less simple, but doable:

select id, processstart, processend, completedtime, max(value) as end_time
from (
  select id, processstart, processend, completedtime,
    processstart as a, processend as b, completedtime as c
  from t
)
unpivot (value for flag in (a as 'A', b as 'B', b as 'C'))
group by id, processstart, processend, completedtime;

fiddle

sys.dbms_debug_vc2coll generates a table of a varchar2 column. Your arguments are timestamps in the mock query and dates in the real query.

As the function wants strings, it must convert the timestamps resp. dates to strings. In order to do so, it uses the according session settings. It seems the setting for dates doesn't include the time, so you get from your DATE column value 2024-05-06 13:53:36 to something like '2024-05-06' which you then convert to a timestamp getting 2024-05-06 00:00:00.000000000.

Avoid implicit conversions. They can even fail! Use explicit conversions, e.g.:

select max(to_timestamp(column_value, 'yyyy-mm-dd hh24:mi:ss')) as end_time 
from sys.dbms_debug_vc2coll(
  to_char(processend, 'yyyy-mm-dd hh24:mi:ss'),
  to_char(completedtime, 'yyyy-mm-dd hh24:mi:ss'),
  to_char(processstart, 'yyyy-mm-dd hh24:mi:ss')
) 
where column_value is not null

You want the greatest of the three dates. The function that comes to mind is GREATEST hence, but Oracle's GREATEST returns null when any argument is null, which makes it a nuisance to work with in scenarios like this. Here is how you would do that in Oracle:

select t.*, nullif(greatest(coalesce(processend,    date '1700-01-01'),
                            coalesce(completedtime, date '1700-01-01'),
                            coalesce(processstart,  date '1700-01-01')
                           ), date '1700-01-01') as end_time