Numeric To Datetime Problem

Hi,

I have a tsql problem where I have two tables both with a field called DateTime. In one table the DateTime is numeric(13) and in the other table the DateTime is varchar(13).

First table has:- (processjobs)
ProductName DateTime
EAC1ST19200707170700
EAC1ST26200507241035
MDM1ST17200707170308

Second Table has:- (productionplanlist)
ProductName DateTime
NFY1ST20200707191448
NHS1ST18200707171053
NHS1ST25200707241036
EAC1ST26200607241035

I need to compare the two tables matching on ProductName and then if the date of the productionplanlist is more recent then it needs to replace the date with the date from the processjobs table. So the newer date is always in the processjobs table.

I have tried the following but i think i am getting the cast into a bit of a twist somewhere.

UPDATE processjobs
SET proc.datetime = prod.datetime
WHERE (cast(prod.datetime as datetime) >= cast(proc.datetime as datetime) and (proc.productname = prod.productname)
FROM processjobs proc, productionplanlist prod

Any help would be greatly appreciated.

Thanks

iamtopgeezer


Ash