I need to compare collumn No to Date in sql
Collumn no substr 7,6
and in database collumn date seem like that how to compare the 2 collumn (the | to just to help)
that means
you are looking at apples and comparing them to oranges
sometimes
Thinking errors
you are looking at bananas and comparing to apples but it wont give any Error Message
SELECT base_dt, updated_dt, gcif_no, br_cd, id_no, cust_nm, birth_dt, gender,
CASE
-- Compare id_no based on characters 6-12 and birth_dt format mm-dd-yyyy
WHEN gender = 'M' AND SUBSTRING(id_no, 6, 7) = DATE_FORMAT(birth_dt, '%m-%d-%Y') THEN 'true'
-- Apply gender conversion for female
WHEN gender = 'F' AND LENGTH(id_no) >= 12 AND SUBSTRING(id_no, 6, 7) = DATE_FORMAT(birth_dt, '%m-%d-%Y') THEN
CASE WHEN SUBSTR(id_no, 7, 2) = DATE_FORMAT(birth_dt, '%y') - 40 THEN 'true' ELSE 'false' END
ELSE 'false'
END AS id_no_birth_dt_match
FROM v_dcif
so this is error first i convert the date format
hi
this means
SUBSTRING(id_no, 6, 7) = DATE_FORMAT(birth_dt, '%m-%d-%Y')
here
SUBSTRING(id_no, 6, 7) is not matching to DATE_FORMAT(birth_dt, '%m-%d-%Y')
not matching
OR
SUBSTR(id_no, 7, 2) = DATE_FORMAT(birth_dt, '%y') - 40
here
SUBSTR(id_no, 7, 2) is not matching to DATE_FORMAT(birth_dt, '%y') - 40
do you know how to debug
select
SUBSTRING(id_no, 6, 7)
, DATE_FORMAT(birth_dt, '%m-%d-%Y')
from
here see the output thats coming .. if its varchar to varchar comparision
if its varchar to date comparision then it gives error
cast varchar as date = date
The query that i make is
Select BASE_DT, UPDATED_DT, GCIF_NO, BR_CD, GCIF_NO, substr(ID_NO, 7, 6) , CUST_NM, to_number (to_char (birth_dt, 'ddmmyy')) as numeric_birth_dt,
CASE
WHEN substr(id_no,7,1) <3 and substr(ID_NO, 7, 6) to_number (to_char (birth_dt, 'ddmmyy')) THEN 'True'
WHEN substr(id_no,7,1)
3 and substr(ID_NO, 7, 6)-400000= to number (to_char (birth_dt, 'ddmmyy')
THEN 'True'
ELSE 'False'
end as COMPARISON RESULT
FROM v_s_dcif Indv
where ID NO IS NOT NULL
AND BIRTH DT IS NOT null and not regexp_like (id_no, '[a-zA-Z]');
the data is show but when iam scroll the error message come
