How to compare dates in snowflake using coalesce

Question

SELECT TO_DATE(TO_CHAR(A.BIRTH_DATE),YYYYMMDD)AS SRC
,B.BIRTH_DATE,A.NAMES
FROM 
NEWDAY A
INNER JOIN
OLDDAY B
ON 
A.SSN = B.SSN
WHERE COALESCE(A.SRC,'99991230')<>COALESCE(B.BIRTH_DATE,'99991230');

1. A.BITH_DATE
   20150312
   20180418
   20001224

   B.BIRTH_DATE
   2015-03-12
   2018-04-18
   2000-12-24
   NOW HOW DO I COMPARE THESE DATES ? THEY ARE IN DIFFERENT FORMAT AND SNOWFLAKE IS THROWING ME AN ERROR

can't parse dates with yyyymmdd format

Please help

Answer 1

The use of COALESCE indicates a need to compare against NULL value. A better approach is using NULL-safe operator IS DISTINCT FROM:

SELECT TRY_TO_DATE(TO_CHAR(A.BIRTH_DATE), 'YYYYMMDD') AS SRC
      ,B.BIRTH_DATE
      ,A.NAMES
FROM NEWDAY A
JOIN OLDDAY B
  ON A.SSN = B.SSN
WHERE A.SRC IS DISTINCT FROM B.BIRTH_DATE

Answer 2

The query you shared has a syntax error:

YYYYMMDD in the TO_DATE(TO_CHAR(A.BIRTH_DATE),YYYYMMDD) <--- Should be 'YYYYMMDD'

Apart from that, it is not easy to repro the issue, unless I convert A.BITH_DATE to date type. So I suspect the issue is about how you read these date values. They are probably already converted to DATE type?

So can you just use this?

SELECT A.BIRTH_DATE AS SRC,B.BIRTH_DATE,A.NAMES
FROM NEWDAY A
INNER JOIN OLDDAY B
ON A.SSN = B.SSN
WHERE COALESCE(SRC,'9999-12-30')<>COALESCE(B.BIRTH_DATE,'9999-12-30');