Compare the date of birth in China ID number with the DOB in DB, show the discrepancy

chinaid-dob-18.sql

SELECT CUSTOMER_ID "Customer ID",
       ID_TEXT "ID Text",
       SUBSTR (ID_TEXT, 7, 8) "Birthday in ID",
       TO_CHAR (DATE_OF_BIRTH, 'DD-MON-YYYY') "Birthday in DB",
       CASE
          WHEN SUBSTR (ID_TEXT, 7, 6) = TO_CHAR (DATE_OF_BIRTH, 'YYYYMM')
          THEN
             'Incorrect day, year and month correct'
          WHEN SUBSTR (ID_TEXT, 11, 4) = TO_CHAR (DATE_OF_BIRTH, 'MMDD')
          THEN
             'Incorrect year, month and day correct'
          WHEN SUBSTR (ID_TEXT, 7, 4) = TO_CHAR (DATE_OF_BIRTH, 'YYYY')
               AND SUBSTR (ID_TEXT, 13, 2) = TO_CHAR (DATE_OF_BIRTH, 'DD')
          THEN
             'Incorrect month, year and day correct'
          WHEN SUBSTR (ID_TEXT, 11, 4) = TO_CHAR (DATE_OF_BIRTH, 'DDMM')
          THEN
             'Swap month and day'
          ELSE
             'Other'
       END
          "Reason"
  FROM CUSTOMER
 WHERE     ID_TYPE = 'CHINAID'
       AND LENGTH (ID_TEXT) = 18
       AND SUBSTR (ID_TEXT, 7, 8) <> TO_CHAR (DATE_OF_BIRTH, 'YYYYMMDD');

chinaid-dob-test.sql

CREATE TABLE CUSTOMER (CUSTOMER_ID NUMBER PRIMARY KEY, ID_TYPE VARCHAR2(10), ID_TEXT VARCHAR2(20), DATE_OF_BIRTH DATE);

INSERT INTO CUSTOMER VALUES (1, 'CHINAID', '123456201307021234', TO_DATE('20130702', 'YYYYMMDD'));

INSERT INTO CUSTOMER VALUES (2, 'CHINAID', '123456201302071234', TO_DATE('20130702', 'YYYYMMDD'));

INSERT INTO CUSTOMER VALUES (3, 'CHINAID', '123456201207021234', TO_DATE('20130702', 'YYYYMMDD'));

INSERT INTO CUSTOMER VALUES (4, 'CHINAID', '123456201307011234', TO_DATE('20130702', 'YYYYMMDD'));

INSERT INTO CUSTOMER VALUES (5, 'CHINAID', '123456201306021234', TO_DATE('20130702', 'YYYYMMDD'));

INSERT INTO CUSTOMER VALUES (6, 'CHINAID', '123456201301011234', TO_DATE('20130702', 'YYYYMMDD'));

chinaid-dob-testresult.txt

+-------------+--------------------+----------------+----------------+---------------------------------------+
| Customer ID |      ID Text       | Birthday in ID | Birthday in DB |                Reason                 |
+-------------+--------------------+----------------+----------------+---------------------------------------+
|           2 | 123456201302071234 |       20130207 | 02-Jul-2013    | Swap month and day                    |
|           3 | 123456201207021234 |       20120702 | 02-Jul-2013    | Incorrect year, month and day correct |
|           4 | 123456201307011234 |       20130701 | 02-Jul-2013    | Incorrect day, year and month correct |
|           5 | 123456201306021234 |       20130602 | 02-Jul-2013    | Incorrect month, year and day correct |
|           6 | 123456201301011234 |       20130101 | 02-Jul-2013    | Other                                 |
+-------------+--------------------+----------------+----------------+---------------------------------------+