# Database Discussions > Microsoft SQL Server 2008 >  Pass date parameter on SQl database to DB2 OpenQuery error

## burakcan

Hi,

I have below openquery.
I am getting below error when I pass the date parameters to open query:
Msg 402, Level 16, State 1, Line 17
The data types varchar and date are incompatible in the add operator.

Is there any one to help me about this please?

My query is as follows;

 DECLARE @ReportDate1 date
 DECLARE @ReportDate2 date
 DECLARE @TSQL varchar(8000)

set @ReportDate1 = (Select prevsun  from CAS_Dates) 
set @ReportDate2 = (Select sat      from CAS_Dates)   	



 SELECT  @TSQL = 'select * from openquery (DB2, 
      ''SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,                   
      T53.DSCH_TYPE,  COUNT(*) AS TOTAL                                 
      FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53            
      WHERE REPORTING_NO = T53.BRANCH                                   
      AND T52.SEC_NUM = T53.SEC_NUM                                     
      AND T52.SEC_TYPE = T53.SEC_TYPE                                   
      AND DATE(T52.CHKLST_TS) >= ''''' + @ReportDate1 + '''''                      
      AND T53.DSCH_TYPE <> ''0''                                          
      AND DATE(T52.CHKLST_TS) <= ''''' + @ReportDate2 + '''''                     
      AND T52.CHKLST_ITEM = 101                                          
      AND BANK_NO = ''004''                                                
      GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
'')'



Thanks

----------


## rmiao

Run 'print @tsql' to check if the statement is correct.

----------


## SDas

burakcan, I am not sure but if that query is being passed to DB2 to process, then the + sign is not used for concatenation.  Instead, try either CONCAT or ||.

----------


## skhanal

The query inside OPENQUERY has to be syntactically correct for the target database, in this case DB2.

----------


## burakcan

> burakcan, I am not sure but if that query is being passed to DB2 to process, then the + sign is not used for concatenation.  Instead, try either CONCAT or ||.


Hi,

Defination says about the concatenation operator:

Both CONCAT and the vertical bars (||) represent the concatenation operator. Vertical bars (or the characters that must be used in place of vertical bars in some countries12) can cause parsing errors in statements passed from one DBMS to another. The problem occurs if the statement undergoes character conversion with certain combinations of source and target CCSIDs12. Thus, CONCAT is the preferable concatenation operator.

When two strings operands are concatenated, the result of the expression is a string. The operands of concatenation must be compatible strings. A binary string cannot be concatenated with a character string, including character strings that are defined as FOR BIT DATA (for more information on the compatibility of data types

So, I am trying the pass two dates parameters to DB2 but I am getting openquery. I have changed my code but still I am getting error. I could not understand the errorr message below.
Thanks for helping

DECLARE @ReportDate1 date
DECLARE @ReportDate2 date
DECLARE @TSQL varchar(8000)

set @ReportDate1 = (Select prevsun  from CAS_Dates) 
set @ReportDate2 = (Select sat      from CAS_Dates)   	

print @reportdate1
print @reportdate2

SELECT  @TSQL = 'SELECT * FROM OPENQUERY(DB2, 
      ''SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,                   
      T53.DSCH_TYPE,  COUNT(*) AS TOTAL                                 
      FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53            
      WHERE REPORTING_NO = T53.BRANCH                                   
      AND T52.SEC_NUM = T53.SEC_NUM                                     
      AND T52.SEC_TYPE = T53.SEC_TYPE                                   
      AND cast(DATE(T52.CHKLST_TS) as varchar(10)) >= ''''' + cast(@ReportDate1 as varchar(10)) + ''''' 
      AND cast(DATE(T52.CHKLST_TS) as varchar(10)) <= ''''' + cast(@ReportDate2 as varchar(10)) + '''''                    
      AND T53.DSCH_TYPE <> ''0''   
      AND T52.CHKLST_ITEM = 101                                          
      AND BANK_NO = ''004''                                                
      GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE  
 '')'
print @TSQL
EXEC (@TSQL)

Here is the output  when I execute it

2010-09-26
2010-10-02
SELECT * FROM OPENQUERY(DB2,
      'SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,                   
      T53.DSCH_TYPE,  COUNT(*) AS TOTAL                                 
      FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53            
      WHERE REPORTING_NO = T53.BRANCH                                   
      AND T52.SEC_NUM = T53.SEC_NUM                                     
      AND T52.SEC_TYPE = T53.SEC_TYPE                                   
      AND cast(DATE(T52.CHKLST_TS) as varchar(10)) >= ''2010-09-26'' 
      AND cast(DATE(T52.CHKLST_TS) as varchar(10)) <= ''2010-10-02''                    
      AND T53.DSCH_TYPE <> '0'   
      AND T52.CHKLST_ITEM = 101                                          
      AND BANK_NO = '004'                                                
      GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE  
 ')
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '0'.

----------


## rmiao

Should you replace " with ' in the code?

----------


## burakcan

Syntax is looks OK. I get this error when I execute it.

----------


## burakcan

Does any one know another way to pass date parameter to DB2 openquery?

----------


## rmiao

Message says incorrect syntax.

----------


## burakcan

so, what is wrong with it?

----------


## rmiao

Tried this:
SELECT * FROM OPENQUERY(DB2,
'SELECT CAS_NUMBER, DIVISION_CODE, T53.DISTRICT, 
T53.DSCH_TYPE, COUNT(*) AS TOTAL 
FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53 
WHERE REPORTING_NO = T53.BRANCH 
AND T52.SEC_NUM = T53.SEC_NUM 
AND T52.SEC_TYPE = T53.SEC_TYPE 
AND cast(DATE(T52.CHKLST_TS) as varchar(10)) >= ''2010-09-26'' 
AND cast(DATE(T52.CHKLST_TS) as varchar(10)) <= ''2010-10-02'' 
AND T53.DSCH_TYPE <> ''0'' 
AND T52.CHKLST_ITEM = 101 
AND BANK_NO = ''004'' 
GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE 
')

----------


## burakcan

Hi,

I have already tried and it works but when I pass the date parameter it doesn't

----------


## burakcan

I have solved my problem like below but I am not happy with it. I just cast date field to VARCHAR.

 DECLARE @Date1 Date 
 DECLARE @Date2 Date 
 DECLARE @ReportDate1 varchar(10)
 DECLARE @ReportDate2 varchar(10)
 DECLARE @TSQL varchar(8000)

set @Date1 = (Select prevsun   from dbo.CAS_Dates)  
set @Date2 = (Select sat       from CAS_Dates)	

set @ReportDate1=CAST(@date1 as varchar(10))
set @ReportDate2=CAST(@date2 as varchar(10))



SET @tsql = 
 'insert into dbo.T_xxxxxxxx
(cas_num,div_code,district,discharge_type,total,ty  pe_status_heloc)' + ' ' +
'select * from openquery (DB2, 
      ''SELECT CAST(CAS_NUMBER AS CHAR(4)), DIVISION_CODE, T53.DISTRICT,                   
      T53.DSCH_TYPE,  COUNT(*) AS TOTAL,''''TYPE'''' as Type_status_Heloc                                 
      FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53            
      WHERE REPORTING_NO = T53.BRANCH                                   
      AND T52.SEC_NUM = T53.SEC_NUM                                     
      AND T52.SEC_TYPE = T53.SEC_TYPE                                   
      AND CAST(T52.CHKLST_TS AS CHAR(10)) >=  ''''' + @ReportDate1 + '''''   
      AND CAST(T52.CHKLST_TS AS CHAR(10)) <=  ''''' + @ReportDate2 + '''''                        
      AND T53.DSCH_TYPE <> ''''0''''  
      AND T52.CHKLST_ITEM = 101
      AND BANK_NO = ''''004''''                                                
      GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
'')'

print @tsql
exec (@tsql)

----------


## rmiao

Whta's result of print @tsql?

----------


## burakcan

insert into dbo.T_xxxxxxxx
(cas_num,div_code,district,discharge_type,total,ty pe_status_heloc) select * from openquery (DB2,
'SELECT CAST(CAS_NUMBER AS CHAR(4)), DIVISION_CODE, T53.DISTRICT,
T53.DSCH_TYPE, COUNT(*) AS TOTAL,''TYPE'' as Type_status_Heloc
FROM CSL.BCSLCLI T52, BIF.VBIF_BRANCH, CSL.BCSLPDM T53
WHERE REPORTING_NO = T53.BRANCH
AND T52.SEC_NUM = T53.SEC_NUM
AND T52.SEC_TYPE = T53.SEC_TYPE
AND CAST(T52.CHKLST_TS AS CHAR(10)) >= ''2010-09-26''
AND CAST(T52.CHKLST_TS AS CHAR(10)) <= ''2010-10-02''
AND T53.DSCH_TYPE <> ''0''
AND T52.CHKLST_ITEM = 101
AND BANK_NO = ''004''
GROUP BY CAS_NUMBER, DIVISION_CODE, T53.DISTRICT,T53.DSCH_TYPE
')

----------


## rmiao

Does above statement work when you execute it directly?

----------


## burakcan

yes,
But I'd like to pass the date parameter. Do you know to to do it?

----------


## rmiao

If all you posted are correct, it should work.

----------


## burakcan

İt works but i need to convert date to string. When İ passing as date parameters i am getting error. So, this my solution it works but i am looking for a solution to use date parameters other than converting them string.

----------

