Conversion failed for linked server

Question

Hi all, I Want to query some field from linked server database table. I would like to convert DTEEFF field which I believe is in varchar format in linked server to date type when query. However I'm getting error conversion error when want to query. The sample data from DTEEFF is like 20220101 in the linked server table. Any idea on how to resolve this? Kindly please help.

SELECT CONVERT(DATE,A.DTEEFF,112) FROM(SELECT * FROM OPENQUERY ([JAV], 'SELECT T1.DTEEFF, FROM "JAV". "RF" T1 INNER JOIN "JAV". "PT" T2 ON T1.CHDRNO=T2.CHDRNO AND T1.RSKNO=T2.RSKNO AND T1.TRANNO=T2.TRANNO ')) A

Answer 1

You can use TRY_CONVERT, it don't raise error, but NULL for not convertable values. https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver16

Answer 2

Hi jn93 Welcome to Q&A Forum; this is a great place to get support, answers, and tips. Thank you for posting your question; I'll be more than glad to help you out.

To adequately assist you with your issue/problem, it would be constructive for us to reproduce your scenario. Please provide us with at least the following things: (1) DDL and sample data population, i.e., CREATE table(s) plus INSERT, T-SQL statements. (2) What you need to do, i.e., logic and your attempt implementation of it in T-SQL. (3) Desired output based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;)

I hope my answer is helpful to you, Your Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

Hi @jn93

The sample data from DTEEFF is like 20220101 in the linked server table.

Is '20220101' a string format or something else? I saw a post where the questioner needed to convert a string to date format, but the conversion failed due to some illegitimate record. The advice given is to use the isdate() function to decide. You can try using this function to see if there are illegal records that cause the conversion to fail. https://learn.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql?view=sql-server-ver16

Best regards, Percy Tang If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".