Skip to content

How to convert 1900-01-01 to null in snowflake

My SQL query output looks like this.

ID UPC
1 2021-05-01
2 1900-01-01

UPC is a date column.

How do I convert 1900-01-01 to null?

I tried following instructions from this, but I am getting SQL compilation error: error line 1 at position 22 invalid identifier ‘DATE’

select ISNULL(CONVERT(date, UPC,23),'') as  UPC from TEST23 

Answer

Using NULLIF:

Returns NULL if expr1 is equal to expr2, otherwise returns expr1.

SELECT ID, NULLIF(UPC, '1900-01-01'::DATE) AS UPC
FROM TEST123