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

Question

question

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

Hi,

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]
,[Action Fourth dimension]
,Instance WHEN ISDATE([Read Date]) = one THEN Cast([Read Date] AS DATETIME) ELSE Cypher Stop AS [Read Date]
,Case WHEN ISDATE([Booked date]) = one THEN Bandage([Booked date] AS DATETIME) ELSE NULL END AS [Booked date]
,CASE WHEN ISDATE([Arrival Time]) = 1 And so CAST([Arrival Time] Equally DATETIME) ELSE NULL END AS [Arrival Time]
,Example WHEN ISDATE([Consultion First Date]) = 1 THEN Cast([Consultionion First Engagement] AS DATETIME) ELSE Zero END AS [Consultion Start Date]
,CASE WHEN ISDATE([Consultion Terminate Date]) = 1 THEN CAST([Consultion End Date] AS DATETIME) ELSE Zilch END Equally [Consultion Terminate Date]
FROM [dbo].[tablestmtrecords]

Source table has mixed date value-->28/12/2018 20:52,10-Mar-xix 11:15:00

Take already tried using simply doesn't work(once again only on 1 particular server-2019 datacenter)

ane)Convert(103,106,107,120)
2)Try_Convert
three)Try_Cast

Thanks

sql-server-full general sql-server-integration-services

You can check the effects of the Set DATEFORMAT and Set up Language settings on return values hither isdate-transact-sql

1 Vote one ·

@YitzhakKhabinsky-0887 can you plz explain me a fleck more than as what i need to?should i do that on the server thats causing issue?

0 Votes 0 ·

when tried locally or on a different server, it works

Did you employ the aforementioned data?

0 Votes 0 ·

aye..the source data is same..

0 Votes 0 ·

In lodge to determine which date ([Read Date], for example) is not like "28/12/2018 20:52" and "10-Mar-nineteen xi:15:00", you can try the adjacent query:

                                                              select [Read Date]  from [dbo].[tablestmtrecords]  where [Read Date] is non cypher and try_convert(datetime, [Read Appointment], 103) is nada

0 Votes 0 ·

Prove more comments

Answer 1

Hello everyone,

As advised past @YitzhakKhabinsky-0887,
I was able to solve the issue by adding 'Gear up LANGUAGE English language' or 'set DATEFORMAT YMD' to the source query.
I assume it has to practice with the regional setting on the server.
Merely i would like know more well-nigh this.. can somebody assistance me out as why information technology worked when i inverse the language? What is to do with set language?

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

@jenniferzen-7686,
Glad to hear that you lot resolved the issue.
That was exactly my proposition.

1 Vote i ·

I appreciate it..and i could non encounter the pick to marking your annotate equally answer...sorry my bad I could non sympathise how information technology was related to linguistic communication as when i checked on both dev and pod..both seemed to have same default language? Can you please explain me a bit more on the behaviour? ready language/dateformat ..whats the differences?

0 Votes 0 ·

@jenniferzen-7686,
Servers have what is called Regional setting for Date and Time format as well as for Languages. And those settings are affecting whatever plan running on them. Including SQL Server. One of your servers has different setting from the balance of the pack.

Instead of changing those settings on the server level, it is possible to do on the SQL Server level for SQL Server simply. That exactly waht you did.

0 Votes 0 ·

Show more than comments

Answer 2

Change all these:

                    Instance WHEN ISDATE([Read Date]) = i THEN Bandage([Read Date] Every bit DATETIME) ELSE NULL Terminate AS [Read Engagement]

to

                    try_cast([Read Appointment] Every bit datetime) Every bit [Read Date]

Past the way, what is the signal with all the Grouping BY? You are non doing whatever aggregation.

@ErlandSommarskog Hullo tried 'try_cast ' already equally mentioned in the original question simply didnt work

0 Votes 0 ·

Yes, I noticed that you said that you had tried try_cast, but y'all did non say how. So try it as I showed for all columns. And remove the Group BY. If it still fails, please show a screenshort of query and error message, so that we know exactly what you ran. (The query you posted, appears to accept an error in 1 of the column names, so I don't know if information technology is accurate.)

0 Votes 0 ·

Answer 3

I think the trouble is the value 28/12/2018 20:52 which is the format dd/mm/yyyy. So yous demand to

split the datetime string into the date and time string,
convert the appointment cord to the engagement format YYYY-MM-DD
convert it back to the string
suspend the fourth dimension string
convert the datetime cord to datetime

See the following case:

                                          DECLARE @d1 varchar(xx);  DECLARE @d2 varchar(20);  Set @d1 = '28/12/2018 xx:52';  SET @d2 = '10-Mar-19 11:xv:00';       SELECT   LEFT(@d1, CHARINDEX(' ', @d1)) Equally d1_Date_String,   SUBSTRING(@d1, CHARINDEX(' ', @d1) + ane, 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(appointment, LEFT(@d1, CHARINDEX(' ', @d1)), 105) AS varchar(ten)) + ' ' + SUBSTRING(@d1, CHARINDEX(' ', @d1) + 1, LEN(@d1) - CHARINDEX(' ', @d1)), 121) Every bit 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(Catechumen(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)

I am shortly having this same mistake issue whenever I try to delete records from the tabular array.

I get the date automatically on my application and tthen create a cavalcade for it in the database table, with the data blazon equally varchar(500)

I outset convert the automatically generated date in my app interface to string so it can exist saved in the database as the set data type varchar.

Whenever I save records, I meet no issue but when I want to delete record, it throws the fault message which is captured in the topic of this post.

I really need to fix this.

0 Votes 0 ·

Answer 4

How-do-you-do @jenniferzen-7686，

Experts accept provided some solutions, do you accept whatsoever updates？

Repeat

Hello @EchoLiu-msft

I tried all the solutions provided only didn't had any luck.
I doubt information technology has to do something the server setting itself rather the cast/convert every bit said earlier, this runs fine on dev environment and local motorcar but just an event on the production surround.

I am looking into the locale setting etc, but if found anything will update in here

0 Votes 0 ·

Error Conversion Failed

question