Good Morning,

I have an existing report created in Cognos v11.0.13 which runs successfully.  The same report ported over to Cognos v11.2.3 fails even when run on the same source data.

1.   Data item and value which is successfully output to my List report:     Order Date = Apr 9, 2024

2.   Data item and value which is successfully output to my List report:     Order Order Time = 212700.000

3.   First expression to reformat only the Order Time:   substring([Order Time],1,2) || ':' || substring([Order Time],3,2) || ':' || substring([Order Time],5,2)
And, the related data item and value which is successfully output to my List report:  Order Time Substring = 21:27:00

4.   Second expression to reformat the Order Date and Order Time into a single data item:   cast([Order Date] + cast([Time Substring], time), timestamp)
And, the related data item and value which is successfully output to my v11.0.13 List report:  Order Date & Time Combined = Apr 9, 2024 21:27:00 PM

The entirety of the four-part example above runs successfully in Cognos v11.0.13. However, the second expression fails in Cognos v11.2.3 with this error:  XQE-DAT-0001 Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: The data types varchar and time are incompatible in the add operator

Does anyone have any hints for a different expression to use in step #4 above?

Quote from: FerdH4 on 17 Jun 2024 10:07:00 AMGood Morning,

I have an existing report created in Cognos v11.0.13 which runs successfully.  The same report ported over to Cognos v11.2.3 fails even when run on the same source data.

1.   Data item and value which is successfully output to my List report:     Order Date = Apr 9, 2024

2.   Data item and value which is successfully output to my List report:     Order Order Time = 212700.000

3.   First expression to reformat only the Order Time:   substring([Order Time],1,2) || ':' || substring([Order Time],3,2) || ':' || substring([Order Time],5,2)
And, the related data item and value which is successfully output to my List report:  Order Time Substring = 21:27:00

4.   Second expression to reformat the Order Date and Order Time into a single data item:   cast([Order Date] + cast([Time Substring], time), timestamp)
And, the related data item and value which is successfully output to my v11.0.13 List report:  Order Date & Time Combined = Apr 9, 2024 21:27:00 PM

The entirety of the four-part example above runs successfully in Cognos v11.0.13. However, the second expression fails in Cognos v11.2.3 with this error:  XQE-DAT-0001 Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: The data types varchar and time are incompatible in the add operator

Does anyone have any hints for a different expression to use in step #4 above?

Hi,

The error seems to be complaining that you can't mathematically add a varchar value and a time value. What data type is [Order Date]? I suspect it needs to be a Date data type for you to be able to add the time value to it. Try casting it as a Date first, before you add the time part.

Cheers!

MF.

Take the string bits which you want to use as the elements of the timestamp and cast them as a timestamp in one go as in this very quickly put together as I work for a living and only have a limited amount to time to faff about on breaks example.

Also writing stuff like this in your report is the least preferable approach. It would be better if you did it in your model, where it would be available to anything which uses the model and, by being common, would avoid differences which could produce confusion or error. Still better would be to perform the calculation to get the column you want during ETL, so its already available to you and the performance hit of doing the calculation is pushed to the ETL time rather than query time.



cast (
substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,1,4 ) || '-'
+
  substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,5,2 )|| '-'
||
substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,7,2 )
||
'T'
||
'12:'/*This would be not hard coded in what you are doing, which is substringing your order order time Also you would probably want to separate the colon out on its own  */
||
'34:'/*See the comment above */
||
'16'  /*See the comment above */
, timestamp)

Quote from: bus_pass_man on 18 Jun 2024 08:36:22 AMTake the string bits which you want to use as the elements of the timestamp and cast them as a timestamp in one go as in this very quickly put together as I work for a living and only have a limited amount to time to faff about on breaks example.

Also writing stuff like this in your report is the least preferable approach. It would be better if you did it in your model, where it would be available to anything which uses the model and, by being common, would avoid differences which could produce confusion or error. Still better would be to perform the calculation to get the column you want during ETL, so its already available to you and the performance hit of doing the calculation is pushed to the ETL time rather than query time.



cast (
substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,1,4 ) || '-'
+
  substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,5,2 )|| '-'
||
substring(
cast( SHIP_DAY_KEY  as varchar(10)) ,7,2 )
||
'T'
||
'12:'/*This would be not hard coded in what you are doing, which is substringing your order order time Also you would probably want to separate the colon out on its own  */
||
'34:'/*See the comment above */
||
'16'  /*See the comment above */
, timestamp)

Man I wish bus_pass_man that I could get changes made in the data model.  Thanks to both of you for sharing, I'll try casting the field as a Date as MFGF suggested.