Question about the UNION operator and its implicit data type conversions
For one, you can certainly simplify your query, for example (I've added schema prefixes, which you should always use):

Select A, B, C
  From dbo.DocumentInfo As Di INNER JOIN dbo.TransmittalSheet TS 
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001'
UNION
Select A, B, C
  From dbo.EquipmentJobPlan As EQ dbo.INNER JOIN TransmittalSheet TS
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001';


Or perhaps even:

SELECT A, B, C
  FROM dbo.TransmittalSheet TS
  LEFT OUTER JOIN dbo.DocumentInfo As Di 
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  LEFT OUTER JOIN dbo.EquipmentJobPlan As EQ
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
WHERE TS.TransmittalNumber = 'TS- 0001' 
AND COALESCE(Di.KeyColumn, EQ.KeyColumn) IS NOT NULL
GROUP BY A, B, C;


(I don't know if the GROUP BY is necessary, because I don't know if you are using UNION on purpose for the intention of removing duplicates. Though I do recommend putting table aliases on all column references, so readers have a clue which columns come from which tables. You may need to use COALESCE here and choose which table takes precedence for any of the output columns if A, B or C don't come from TransmittalSheet.)

Now, you say that when you use a UNION you get a conversion error, because in one of the tables column C is a varchar and in another it is a smalldatetime. Your options are:


To fix that. If you are considering those columns to be the same and compatible, they should be of the same data type and contain like data. Which means changing the data type after you remove any data that can't be directly converted to smalldatetime.
If the varchar column needs to have different data that is not compatible with smalldatetime, then perhaps it is best to present that as a different output column in your UNION query.
After that, you can apply an explicit cast on the smalldatetime type so that it can be converted to varchar, instead of the way SQL Server will do it (which is to try to convert the string to a date). If you want to only present valid smalldatetime data, then you can CAST the other way, but use a CASE expression so that the conversion is only attempted on valid data. The implicit conversion can follow the rules of data type precedence, but if the varchar column contains any value that can't be converted to smalldatetime, it fails with Msg 295, as you've discovered. This can happen even if those rows don't appear in the output - the conversion can occur before the rows are filtered out (I've written about this here, here, here and here and it was reported as a bug on Connect (RIP). And because you're using UNION instead of UNION ALL, you may be more likely to hit invalid values because the additional DISTINCT SORT operations this (usually) adds, which can change the way SQL Server will optimize the overall plan.


So, if you can't benefit from the re-writes above (or if they produce different results for some reason), and you can't do 1. or 2., then I would re-write the UNION version either like this:

-- against the table where C is smalldatetime:
SELECT A, B, CONVERT(CHAR(10), C, 120)
FROM ...

-- or against the table where C is varchar:
SELECT A, B, CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM ...


A more localized example you can play with:

CREATE TABLE dbo.foo(A INT, B INT, C VARCHAR(75));
CREATE TABLE dbo.bar(A INT, B INT, C SMALLDATETIME);    
INSERT dbo.foo(A,B,C) VALUES(1,2,'three'),(2,4,'20150101');
INSERT dbo.bar(A,B,C) VALUES(1,2,'20050101'),(2,4,'20050101');
GO

-- these both work of course:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1;
SELECT A,B,CONVERT(SMALLDATETIME,C) FROM dbo.foo WHERE ISDATE(C) = 1;
GO

-- fails:
SELECT A,B,C FROM dbo.foo
UNION
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds, but may not always:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1
UNION 
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds:
SELECT A, B, C = CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM dbo.foo
UNION 
SELECT A, B, C FROM dbo.bar;

-- succeeds:
SELECT A, B, C FROM dbo.foo
UNION 
SELECT A, B, C = CONVERT(VARCHAR(10), C, 120) FROM dbo.bar;
GO

DROP TABLE dbo.foo, dbo.bar;


Finally, please don't CAST( AS Char) with no length, or any other variable type.