question

jenniferzen-7686 avatar image
0 Votes"
jenniferzen-7686 asked CEO-8149 commented

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

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

sql-server-generalsql-server-integration-services
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You can check the effects of the SET DATEFORMAT and SET LANGUAGE settings on return values here isdate-transact-sql


1 Vote 1 ·

@YitzhakKhabinsky-0887 can you plz explain me a bit more 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 use the same data?

0 Votes 0 ·

yes..the source data is same..

0 Votes 0 ·

In order to determine which date ([Read Date], for example) is not like “28/12/2018 20:52” and “10-Mar-19 11:15:00”, you can try the next query:

 select [Read Date]
 from [dbo].[tablestmtrecords]
 where [Read Date] is not null and try_convert(datetime, [Read Date], 103) is null



0 Votes 0 ·
Show more comments
jenniferzen-7686 avatar image
0 Votes"
jenniferzen-7686 answered jenniferzen-7686 edited

Hello everyone,

As advised by @YitzhakKhabinsky-0887,
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 :)

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

1 Vote 1 ·

I appreciate it..and i could not see the option to mark your comment as answer...sorry my bad I could not understand how it was related to language 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? set 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 any program running on them. Including SQL Server. One of your servers has different setting from the rest 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 only. That exactly waht you did.

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog Hello tried 'try_cast ' already as mentioned in the original question but didnt work

0 Votes 0 ·

Yes, I noticed that you said that you had tried try_cast, but you did not 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 have an error in one of the column names, so I don't know if it is authentic.)

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered CEO-8149 commented

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)






· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am presently having this same error issue whenever I try to delete records from the table.

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

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

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

I really need to fix this.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered jenniferzen-7686 edited

Hi @jenniferzen-7686

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

Echo

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EchoLiu-msft

I tried all the solutions provided but didn't had any luck.
I doubt it has to do something the server setting itself rather the cast/convert as said earlier, this runs fine on dev environment and local machine but just an issue on the production environment.

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

0 Votes 0 ·