One of the column you return in one of your "selects" (I think it's a first one

) is of smalldatetime data type (we don't know which column exactly, as you didn't post any ddl and code looks like noodles a bit). The position of this column in this "select" corresponds to position of "character" data type column from second "select", where character data cannot be converter implicitly to datetime.

First thing I would advise you to do is to format your code, so you could read it easily. It can help you to find this column just by comparing to select lists. You can format it to something like:

Select top 10 =

CASE

WHEN a.id_value IS NULL THEN 'Not Company'

ELSE a.id_value

END AS [Company Reg No]

,a.sic_code AS [Industry]

,a.tin AS [Tax ID]

,a.tin_cert_dt AS [Registration Date]

,a.status AS [Company Status]

, ...

etc.

You will see that code formatted as above is easier to maintain and debug.