Hello,
SSIS package fails with the above conversion error only on one of the server.
when tried locally or on a different server, it works ? can somebody help me understand the issue plz?
My syntax from the source table
SELECT
[Stmt Source]
,[Stmt No]
,[Stmt PRN]
,[Stmt Outcome]
,[Activity Time]
,CASE WHEN ISDATE([Read Date]) = 1 THEN CAST([Read Date] AS DATETIME) ELSE NULL END AS [Read Date]
,CASE WHEN ISDATE([Booked date]) = 1 THEN CAST([Booked date] AS DATETIME) ELSE NULL END AS [Booked date]
,CASE WHEN ISDATE([Arrival Time]) = 1 THEN CAST([Arrival Time] AS DATETIME) ELSE NULL END AS [Arrival Time]
,CASE WHEN ISDATE([Consultion Start Date]) = 1 THEN CAST([Consultionion Start Date] AS DATETIME) ELSE NULL END AS [Consultion Start Date]
,CASE WHEN ISDATE([Consultion End Date]) = 1 THEN CAST([Consultion End Date] AS DATETIME) ELSE NULL END AS [Consultion End Date]
FROM [dbo].[tablestmtrecords]
Source table has mixed date value-->28/12/2018 20:52,10-Mar-19 11:15:00
Have already tried using but doesn't work(again only on one particular server-2019 datacenter)
1)Convert(103,106,107,120)
2)Try_Convert
3)Try_Cast
Thanks