0

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???

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
Carl Almasan
  • 1
  • 2
    You can't have different data types in a single column, if you want to have a string value intermixed with dates you need to cast everything as a string. – Stu May 19 '23 at 14:09
  • with another case statement that looks for 'N/A' and casts it back to 1800-01-01... – Kurt May 19 '23 at 14:09

1 Answers1

0

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
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51