topic Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server in Talend Studio

How to pass date value embedded within single quotes to call stored procedure on SQL Server

mra802003 — Tue, 26 Sep 2017 21:31:40 GMT

Hi,

I am trying to call stored procedure using tJDBCSP component after establishing connection to MSSQLServer using tJDBCConnection.

The stored procedure expects the argument (date value) to be passed within single quotes as '01-28-2016'.

I have used tMap to convert the date in string format to date format using TalendDate.parseDate("MM-dd-yyyy",row2.DateValue). This is working as expected. If I use "'"+row2.DateValue+"'" in the tMap with column datatype as String and pass this value to call stored procedure, it throws the error as "Conversion failed when converting date and/or time from character string". The stored procedure is expecting the argument to be in date datatype format embedded within single quotes.

Any suggestions on how to go about this?

Regards

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

Anonymous — Tue, 26 Sep 2017 22:19:52 GMT

I believe I have seen this before, but can't test my suspicion at the moment. I suspect that you may need to supply the date in the format 'yyyy-MM-dd' or 'yyyyMMdd'.

Try this in your tMap.....

"'"+routines.TalendDate.formatDate("yyyyMMdd", TalendDate.parseDate("MM-dd-yyyy",row2.DateValue))+"'"

....or....

"'"+routines.TalendDate.formatDate("yyyy-MM-dd", TalendDate.parseDate("MM-dd-yyyy",row2.DateValue))+"'"

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

mra802003 — Tue, 26 Sep 2017 22:33:23 GMT

This will treat the entire value as string and the requirement is to pass the date within single quotes without converting to string.

The column datatype in tMap is defined as Date format "MM-dd-yyyy".

If I try the way you suggested, I am getting the following error:

Type mismatch: cannot convert from String to Date

Regards

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

Anonymous — Tue, 26 Sep 2017 23:08:24 GMT

You cannot pass a Date type within single quotes. You are mixing types. Anything supplied within single quotes is a String (or Varchar/char). A Date is essentially just a complex number type. SQL Server will implicitly convert your date as a String IF it is in a format which it understands.

I suspect that we are missing some information here. If your SP requires a Date class (what the tMap column is set to according to your description), you should be able to supply your data as a Date. If this is the case this should work.....

TalendDate.parseDate("MM-dd-yyyy",row2.DateValue)

.... if the DateValue is in the format "MM-dd-yyyy". However, I suspect that your SP may require the date as a String and that the column type of the tMap may be wrong.

Either way, it would be easier to figure this out if you could take a screenshot of your SP component config, your tMap config and your SP.

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

mra802003 — Wed, 27 Sep 2017 15:25:58 GMT

I knew I was mixing two datatypes but wanted to know if there is any work around. I have uploaded the pics of all that you asked for.

The error in the attached pic is happening when the data type for input argument column in tMap is defined as string.

Regards

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

Anonymous — Wed, 27 Sep 2017 15:51:28 GMT

The procedure is certainly expecting a Varchar. I believe (but can't test this unfortunately) that the datetime2(7) type is means that the the date is being converted from a Varchar to a DateTime2 to 7 fractions of a second. As such, the implicit conversion is likely failing because you are not supplying enough information in the String.

Try making sure your String date looks like below....

'2017-09-27 00:00:00.0000000'

Essentially just hardcode " 00:00:00:0000000" to the end of your date and wrap it in single quotes.

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

mra802003 — Wed, 27 Sep 2017 16:15:13 GMT

I tried using the hard coded value as "01-28-2016 00:00:00.0000000" in tFixedFlowInput.

I tried with the default format as well "YYYY-MM-DD hh:mm:ss[.nnnnnnn]", but no luck.

This value gets embedded in single quotes in tMap.

I still get the same error message as shown below.

Exception in component tJDBCSP_1
com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

When we execute this stored procedure manually on SQLServer window, we use the command as follows and it returns correct values in two output arguments - FiscalWeek and FiscalYear.

exec [BizTalk].[MMX_GetFiscalWeekAndYear] '01-28-2016'

Regards

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

Anonymous — Wed, 27 Sep 2017 16:59:53 GMT

OK, I completely missed something when you showed me your images. It is difficult to explain this, so I have knocked up an example using a SP similar to yours.

Below is the layout of my job. The tFixedFlowInput simply supplies the date value. I have set it to "2017-09-27". I have NOT added single quotes.

Below is the SP config. The schema has 2 columns; currentDateTime (String) and out1 (Object).

out1 is to recieve a datarow, not an idividual value.

Next I have added a tParseRecordSet. This is needed to strip the values from the recordset.

Note that the "Value" corresponds to the columns output by your SP.

When I run this, I get the values back correctly.

The bit I was missing (or not really thinking about) was the confusion over single quotes required around the date. Since Talend is passing a known String value (the date) we do not need to supply single quotes. That is handled for us. It was the single quotes causing your error and I presume it was an issue with returning the values correctly that led to you assuming that the single quotes were required.

Re: How to pass date value embedded within single quotes to call stored procedure on SQL Server

mra802003 — Wed, 27 Sep 2017 17:22:24 GMT

Thanks a lot. It worked this time.