Could someone kindly help me rewrite the correct syntax in order to use a date and text field in a case statement. However I need to find the maximum date of the date field, so am assuming that I need to to use both to_char and to_date
case when location = 'DOSPR' then Max(TO_CHAR(TO_DATE(datetime,'YY/MM/DD'),' YY/MM/DD')) else '' end as test,
Unfortunately, it is not clear from your post, what is the data type of the DATETIME column.
If it is CHAR/VARCHAR2 in 'YY/MM/DD' format (to which leads the usage of conversion functions), then they have no effect except eventual failure for some values representing wrong (e.g. non-existent) date.
If it is DATE, then (as TO_DATE does not support DATE parameter) DATETIME is implicitly converted to VARCHAR2 with NLS_DATE_FORMAT format mask which will lead to undeterministic behaviour with possibilty of failure when NLS_DATE_FORMAT is set to non-matched value.
In both cases (as 'YY/MM/DD' format is ordered by its DATE representation), why not simply use MAX( DATETIME )?