Conversion failed when converting date and/or time from character string only on one of the server

Question

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

Answer 1

Hello everyone,

As advised by @Yitzhak Khabinsky ,
I was able to solve the issue by adding 'SET LANGUAGE English' or 'set DATEFORMAT YMD' to the source query.
I assume it has to do with the regional setting on the server.
But i would like know more about this.. can somebody help me out as why it worked when i changed the language? What is to do with set language?

Thanks all for your help...appreciate it :)

Answer 2

Change all these:

CASE WHEN ISDATE([Read Date]) = 1 THEN CAST([Read Date] AS DATETIME) ELSE NULL END AS [Read Date]

to

try_cast([Read Date] AS datetime) AS [Read Date]

By the way, what is the point with all the GROUP BY? You are not doing any aggregation.

Answer 3

I think the problem is the value 28/12/2018 20:52 which is the format dd/mm/yyyy. So you need to

1. split the datetime string into the date and time string,
2. convert the date string to the date format YYYY-MM-DD
3. convert it back to the string
4. append the time string
5. convert the datetime string to datetime

See the following example:

DECLARE @d1 varchar(20);  
DECLARE @d2 varchar(20);  
SET @d1 = '28/12/2018 20:52';  
SET @d2 = '10-Mar-19 11:15:00';  
  
SELECT   
LEFT(@d1, CHARINDEX(' ', @d1)) AS d1_Date_String,   
SUBSTRING(@d1, CHARINDEX(' ', @d1) + 1, LEN(@d1) - CHARINDEX(' ', @d1)) AS d1_Time_String,   
LEFT(@d2, CHARINDEX(' ', @d2)) AS d2_Date_String,   
SUBSTRING(@d2, CHARINDEX(' ', @d2) + 1, LEN(@d2) - CHARINDEX(' ', @d2)) AS d2_Time_String,  
CONVERT(datetime, CAST(CONVERT(date, LEFT(@d1, CHARINDEX(' ', @d1)), 105) AS varchar(10)) + ' ' + SUBSTRING(@d1, CHARINDEX(' ', @d1) + 1, LEN(@d1) - CHARINDEX(' ', @d1)), 121) AS d1_Datetime,  
CONVERT(datetime, CAST(CONVERT(date, LEFT(@d2, CHARINDEX(' ', @d2)), 105) AS varchar(10)) + ' ' + SUBSTRING(@d2, CHARINDEX(' ', @d2) + 1, LEN(@d2) - CHARINDEX(' ', @d2)), 121) AS d2_Datetime;  

So you can use the following code to convert the datetime string to the datetime format yyyy-mm-dd hh:mm:ss:

CONVERT(datetime, CAST(CONVERT(date, LEFT([DATE_STRING_COLUMN], CHARINDEX(' ', [DATE_STRING_COLUMN])), 105) AS varchar(10)) + ' ' + SUBSTRING([DATE_STRING_COLUMN], CHARINDEX(' ', [DATE_STRING_COLUMN]) + 1, LEN([DATE_STRING_COLUMN]) - CHARINDEX(' ', [DATE_STRING_COLUMN])), 121)  





 
 

Answer 4

Hi @jennifer zen ，

Experts have provided some solutions, do you have any updates？

Echo