Oracle DB 12.1.0.2 EE
Solaris 11
Hello Team,
please note that we have 1 column 'GEN_STRING_5_V' which is varchar2. I would like to convert the data to date.
select FILE_NAME_V,GEN_STRING_2_V,GEN_STRING_5_V,GEN_STRING_4_V,
to_date((case when
length(GEN_STRING_5_V) = 11
then
TO_CHAR(TO_DATE(GEN_STRING_5_V,'DD-MON-YYYY'),'DD/MM/YYYY')
when
length(GEN_STRING_5_V) = 10
THEN
TO_CHAR(TO_DATE(GEN_STRING_5_V,'DD/MM/YYYY'),'DD/MM/YYYY')
else
TO_CHAR(TO_DATE(GEN_STRING_5_V,'DD-MON-YY'),'DD/MM/YYYY')
end),'DD/MM/YYYY') payment_date,STATUS_V,
REJECTED_REASON_V,GEN_STRING_6_V,GEN_STRING_8_V,UPLOAD_KEY_V,GEN_STRING_11_V
from bireport.tmp_upload_dtls
where UPLOAD_KEY_V in ('BULMYTP','BULPPMT')
and length(GEN_STRING_5_V) >= 9
and TO_DATE(GEN_STRING_5_V,'DD-MON-YY')
= '02-FEB-22';
Can you please help
Thanks,
Roshan