How to find out which records are causing an exception in a select statement?
Try this query:

SELECT tc.pushedClientReference as ClientReference, tc.pushedStatusId as [Status], 
tc.createdOn 
FROM tablebackups.dbo.tmp_colagbgdata tc
where TRY_CAST(tc.createdOn as datetime) is null
and tc.createdOn is not null


The TRY_CAST function returns null when a CAST operation is impossible, so the above query will return the rows where converting the createdOn column to datetime is causing an error.

If you have a particular preference there's also TRY_CONVERT.

Now that we know the SELECT query is feeding an INSERT, I'd suggest that tc.createdon in the SELECT list be explicitly converted using TRY_CAST or TRY_CONVERT.