when trying to run the below query iam getting Invalid Time error.
WHEN (LENGTH(b.vndr_no)=6 or LENGTH(b.vndr_no) is null)
THEN CAST(SUBSTR(B.Vndr_no,1,2)|| ':' || SUBSTR(B.Vendor_no,3,2)|| ':' || SUBSTR(B.Vndr_no,5, 2) AS TIME)
ELSE cast(b.vndr_no as time)
END) AS TRAN_END_TIME from TableA
The problem is Vndr_no colunm is a Varchar(1500) and needs to converted to time.
In vndr_no column we have few rows with length=6 and few as null for both these iam able to insert value to a table but there are few rows in this column which are timestamp or length<>6 so while converting these to time iam getting Invalid time error.
Can anyone please help me to solve this issue.Need to convert all rows to time datatype.
Thanks in advance.
What's the actual data in this column, always a time or a timestamp or something else, too?
Why is that column defined as Varchar(1500) then?