Error - Talend tMSSQLInput

Four Stars

Error - Talend tMSSQLInput

Hi,

 

I have a problem with tMSSQl Input when I use a date to inform where clause, but, all the time Talend Studio return me the message: "tMSSqlInput_1 java.sql.SQLException: The conversion of a varchar data type to a DateTime data type resulted in an out-of-range value."

 

I look DbTypes on the schema, set date, and DateTime, but I don't understand and find any solution for my problem. In my database, the type about this column is DateTime.

 

Note: when I put, in the where clause, a date, the component works perfectly: AND HCA3.DATALT  BETWEEN '2018-04-30' AND '2018-06-11', but when I put de context information, the component doesn't work: AND HCA3.DATALT  BETWEEN '" + context.dt_inicio + "' AND '" + context.dt_fim + "'.

 

Can someone help me?

Fifteen Stars TRF
Fifteen Stars

Re: Error - Talend tMSSQLInput

How are defined context variables, as String?

TRF
Four Stars

Re: Error - Talend tMSSQLInput

Yes,

My context is string.
Nine Stars

Re: Error - Talend tMSSQLInput

Hi,

 

From the error you're getting, its SQL parsing the string value of a date into a date thats the problem.

 

Typically, but not always, dates are formatted yyyy-MM-dd, so check that is how you are forming the textual versions of your dates. This kind of problem can often be masked unless you're using days after the 12th of the month.

 

I think the tMSSQLInput component also has a global variable QUERY which is the text actually passed to SQL Server, check that if you copy/paste this into SSMS it works correctly as well.

 

Thanks

 

David

Regards David
Dont forget to give Kudos when an answer is helpful or mark the answer as the solution.
Four Stars

Re: Error - Talend tMSSQLInput

Hi,

 

I found the solution! Smiley Very Happy

 

To MSSQLinput work well, I need to develop some variables in SQL query to convert my context string to date format.

 

Look:

 

"declare @DataInicial datetime, @DataFinal datetime;

set @DataInicial= Convert(datetime, '" + context.dt_inicio + "', 103);
set @DataFinal= Convert(datetime, '" + context.dt_fim + "', 103)

 

and the where clause I put the variable that content:

 

AND HCA3.DATALT  BETWEEN @DataInicial AND @DataFinal)

"

 

Thanks for your time!

 

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Modern Data Engineering in the Cloud

Learn about modern data engineering in the Cloud

Watch Now

How to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud reg...

Learn how to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud registries

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog