How to resolve informix date error?

Question

I have the below SQL query in my code

    String sql = "select t.card_num, \n" +
    " right(t.card_num,4) as CardLast4 \n" +
    ",t.invoice_trim as Ticket_number  \n" +
    ",t.pos_date \n" +
    ",tiu.info as Vehicle_number \n" +
    ",t.contract_id \n" +
    ",tio.info as Odometer \n" +
    ",l.address_1 \n" +
    "\n" +
    ",CASE -- different for CAN vs USA query --Field #7 \n" +
    "  when l.src_country = 'USA' then prodUSA.description \n" +
    "  when l.src_country = 'CAN' then prodCAN.description \n" +
    "  else '*** ERROR ***' \n" +
    "  END as product_name \n" +
    "  \n" +
    "\n" +
    " ,tl.qty --Field #8 \n" +
    "\n" +
    ",CASE --different for CAN vs USA --Field #9 \n" +
    " when l.src_country = 'USA' then tl.ppu \n" +
    "else tlb.base_net_ppu end as base_ppu \n" +
    "\n" +
    " ,tltf.tax_rate as fet_rate \n" +
    ",tltp.tax_rate as pft_rate \n" +
    "\n" +
    ",CASE \n" +
    "when l.src_country = 'USA' then tl.ppu \n" +
    "else (tlb.base_net_ppu + tlb.unit_cpl_rate) end as net_ppu \n" +
    "\n" +
    ",tltg.amount as gsthstfnt \n" +
    ",t.pref_total as net_amount \n" +
    ",t.location_id as location_number \n" +
    ",t.trans_date \n" +
    ",l.city as City \n" +
    ",l.state as Province  \n" +
    ",t.carrier_id as Account_number \n" +
    ",cm.second_line as Customer_Field_1 \n" +
    ",tmp.trans_meta_data as policy \n" +
    ",tltv.tax_rate as MFT_rate \n" +
    ",tltct.tax_rate as Carbon_tax_rate \n" +
    ",tltpst.amount as pst_amount \n" +
    ",currency \n" +
    "\n" +
    "from transaction as t \n" +
    "left outer join contract as c \n" +
    "on t.contract_id = c.contract_id \n" +
    "left outer join trans_info as tiu \n" +
    "on t.trans_id = tiu.trans_id \n" +
    "and tiu.type = \"UNIT\" \n" +
    "left outer join trans_info as tio \n" +
    "on t.trans_id = tio.trans_id \n" +
    "and tio.type = \"ODRD\" \n" +
    "left outer join location as l \n" +
    "on t.location_id = l.location_id \n" +
    "left outer join trans_line as tl \n" +
    "on t.trans_id = tl.trans_id \n" +
    "\n" +
    "left outer join products as prodUSA \n" +
    "on prodUSA.num = tl.num and prodUSA.fps_partner = 'TCH' \n" +
    "left outer join products as prodCAN \n" +
    "on prodCAN.num = tl.num and prodCAN.fps_partner = 'IMPERIAL' \n" +
    "\n" +
    "left outer join trans_line_baseppu as tlb \n" +
    "on t.trans_id = tlb.trans_id \n" +
    "\n" +
    "\n" +
    "  \n" +
    "left outer join trans_line_tax as tltf \n" +
    "on t.trans_id = tltf.trans_id \n" +
    "and tltf.tax_cd = \"FET\" \n" +
    "and tltf.gross_net_flag = \"N\" \n" +
    "and tltf.exempt_flag = \"N\" \n" +
    "\n" +
    "\n" +
    "left outer join trans_line_tax as tltp \n" +
    "on t.trans_id = tltp.trans_id \n" +
    "and tltp.tax_cd = \"PFT\" \n" +
    "and tltp.gross_net_flag = \"N\" \n" +
    "and tltp.exempt_flag = \"N\" \n" +
    "\n" +
    "left outer join trans_line_tax as tltg \n" +
    "on t.trans_id = tltg.trans_id \n" +
    "and tltg.tax_cd IN (\"GST\",\"HST\",\"FNT\") \n" +
    "and tltg.gross_net_flag = \"N\" \n" +
    "and tltg.exempt_flag = \"N\" \n" +
    "\n" +
    "left outer join card_misc as cm \n" +
    "on t.card_num = cm.card_num \n" +
    "and t.carrier_id = cm.carrier_id \n" +
    "\n" +
    "left outer join trans_meta as tmp \n" +
    "ON t.trans_id = tmp.trans_id \n" +
    "and tmp.trans_meta_type_id = \"CPOL\" \n" +
    "\n" +
    "left outer join trans_line_tax as tltv \n" +
    "on t.trans_id = tltv.trans_id \n" +
    "and tltv.tax_cd = \"VTT\" \n" +
    "and tltv.gross_net_flag = \"N\" \n" +
    "and tltv.exempt_flag = \"N\" \n" +
    "\n" +
    "left outer join trans_line_tax as tltct \n" +
    "on t.trans_id = tltct.trans_id \n" +
    "and tltct.tax_cd = \"VTT\" \n" +
    "and tltct.gross_net_flag = \"N\" \n" +
    "and tltct.exempt_flag = \"N\" \n" +
    "\n" +
    "left outer join trans_line_tax as tltpst \n" +
    "on t.trans_id = tltpst.trans_id \n" +
    "and tltpst.tax_cd in (\"PST\",\"QST\") \n" +
    "and tltpst.gross_net_flag = \"N\" \n" +
    "and tltpst.exempt_flag = \"N\" \n" +
    " where t.trans_date between "+ getParam("jrs.param$p_startDate") +" and "+ getParam("jrs.param$p_endDate")+"\n" +
    "\n" +
    "\n";

Below is the query result which i see when i debug my code:

select t.card_num, 
 right(t.card_num,4) as CardLast4 
,t.invoice_trim as Ticket_number  
,t.pos_date 
,tiu.info as Vehicle_number 
,t.contract_id 
,tio.info as Odometer 
,l.address_1 

,CASE -- different for CAN vs USA query --Field #7 
  when l.src_country = 'USA' then prodUSA.description 
  when l.src_country = 'CAN' then prodCAN.description 
  else '*** ERROR ***' 
  END as product_name 
 ,tl.qty --Field #8 

,CASE --different for CAN vs USA --Field #9 
 when l.src_country = 'USA' then tl.ppu 
else tlb.base_net_ppu end as base_ppu 

 ,tltf.tax_rate as fet_rate 
,tltp.tax_rate as pft_rate 

,CASE 
when l.src_country = 'USA' then tl.ppu 
else (tlb.base_net_ppu + tlb.unit_cpl_rate) end as net_ppu 

,tltg.amount as gsthstfnt 
,t.pref_total as net_amount 
,t.location_id as location_number 
,t.trans_date 
,l.city as City 
,l.state as Province  
,t.carrier_id as Account_number 
,cm.second_line as Customer_Field_1 
,tmp.trans_meta_data as policy 
,tltv.tax_rate as MFT_rate 
,tltct.tax_rate as Carbon_tax_rate 
,tltpst.amount as pst_amount 
,currency 

from transaction as t 
left outer join contract as c 
on t.contract_id = c.contract_id 
left outer join trans_info as tiu 
on t.trans_id = tiu.trans_id 
and tiu.type = "UNIT" 
left outer join trans_info as tio 
on t.trans_id = tio.trans_id 
and tio.type = "ODRD" 
left outer join location as l 
on t.location_id = l.location_id 
left outer join trans_line as tl 
on t.trans_id = tl.trans_id 

left outer join products as prodUSA 
on prodUSA.num = tl.num and prodUSA.fps_partner = 'TCH' 
left outer join products as prodCAN 
on prodCAN.num = tl.num and prodCAN.fps_partner = 'IMPERIAL' 

left outer join trans_line_baseppu as tlb 
on t.trans_id = tlb.trans_id 



left outer join trans_line_tax as tltf 
on t.trans_id = tltf.trans_id 
and tltf.tax_cd = "FET" 
and tltf.gross_net_flag = "N" 
and tltf.exempt_flag = "N" 


left outer join trans_line_tax as tltp 
on t.trans_id = tltp.trans_id 
and tltp.tax_cd = "PFT" 
and tltp.gross_net_flag = "N" 
and tltp.exempt_flag = "N" 

left outer join trans_line_tax as tltg 
on t.trans_id = tltg.trans_id 
and tltg.tax_cd IN ("GST","HST","FNT") 
and tltg.gross_net_flag = "N" 
and tltg.exempt_flag = "N" 

left outer join card_misc as cm 
on t.card_num = cm.card_num 
and t.carrier_id = cm.carrier_id 

left outer join trans_meta as tmp 
ON t.trans_id = tmp.trans_id 
and tmp.trans_meta_type_id = "CPOL" 

left outer join trans_line_tax as tltv 
on t.trans_id = tltv.trans_id 
and tltv.tax_cd = "VTT" 
and tltv.gross_net_flag = "N" 
and tltv.exempt_flag = "N" 

left outer join trans_line_tax as tltct 
on t.trans_id = tltct.trans_id 
and tltct.tax_cd = "VTT" 
and tltct.gross_net_flag = "N" 
and tltct.exempt_flag = "N" 

left outer join trans_line_tax as tltpst 
on t.trans_id = tltpst.trans_id 
and tltpst.tax_cd in ("PST","QST") 
and tltpst.gross_net_flag = "N" 
and tltpst.exempt_flag = "N" 
where t.trans_date between 2016-09-26 and 2016-10-10

Now when i execute the query result in my SQL editor, I am getting a SQLException:

"ERROR java.sql.SQLException: It is not possible to convert between the specified types"

can someone please help me, i tried putting quotes around the date fields. But that didn't work.

Answer 1

Whilst not disagreeing with Jonathan Leffler's comment about the actual problem being obscured by all the noise, I think your problem is this line:

" where t.trans_date between "+ getParam("jrs.param$p_startDate") +" and "+ getParam("jrs.param$p_endDate")+"\n" 

which parses to this:

where t.trans_date between 2016-09-26 and 2016-10-10

Date fields need to be enclosed in quotes, lest they be interpreted as arithmetic (2016 - 9 - 26 = 1981), so your predicate is reducing to:

where t.trans_date between 1981 and 1996

... clearly not what you want, and which explains the server's complaint about data types. So try adding escaped quotes:

" where t.trans_date between \""+ getParam("jrs.param$p_startDate") +"\" and \""+ getParam("jrs.param$p_endDate")+"\"\n"

As an aside, Informix is agnostic about single or double quotes, so you can make your code a lot more readable by using single-quotes wherever you've currently got escaped double-quotes. Other engines that are fussy about this (SQL Server for example) expect single-quotes for strings anyway.