How can I change a datetime value to a string, but keep the other values at Date value?

Question

I'm dealing with a dateTime such as '1800-01-01 00:00:00' and I want to convert it to a string = 'N/A', but keep everything else as a dateTime.

I tried converting it to a string (To_char(date_time)) AS date_time2 in a subquery and in the main query I created a case statement:

WHEN date_time Like '1800-01-01 00:00:00' THEN 'N/A' ELSE date_time. 

It would work, however when I want to convert it back to a dateTime value the 'N/A' string disappears since it can't be converted to time. How can I avoid this problem???

Answer 1

This won't supply "N/A" but it will suppress the default date of 1800-01-01:

SELECT NULLIF(datecol, DATE '1800-01-01') AS new_datecol
FROM your_table;

It is simply not possible to retain date/time characteristics for all rows except some that output "N/A". Either that whole column remains date/time, or the whole column is converted to strings, as seen below:

SELECT 
    CASE WHEN datecol = DATE '1800-01-01' THEN 'N/A'
         ELSE CAST(datecol AS FORMAT 'YYYY-MM-DD') -- format to "taste"
    END AS string_column_now
FROM your_table;

nb: if you want to order by the date column, but don't want the 1800-01-01 as the first rows:

ORDER BY NULLIF(datecol, DATE '1800-01-01') ASC NULLS LAST