05-24-2018 01:07 PM
I want to convert a sas date to Teradata date in the where clause below:
create table temp_4 as
select a.counter, max(c.ID) as CI_ID
from temp_3 a
left join table.abc b ON a.H_ID = b.X_ID
inner join table.def c ON b.CI_ID = c.CI_ID
left join table.ghi d ON c.CI_ID = d.CI_ID
where b.type ='HI' and substr(c.CI_LAST_NM,1,12) = a.Surname and substr(c.CI_FRST_NM,1,7) = a.First_Name
and c.CI_BIR_DT = a.DOB and c.CI_SEX_C = a.gen
group by a.counter
c.CI_BIR_DT = a.DOB CI_BIR_DT is a Teradata date and a.DOB is the sas date.
I tried a few options but failed to get the correct result. Please advise.