error in passing date value in sql query - not executing

[harish0191]

i have tried giving value in different format but nothing is working out

 

#example 1

default value of  $P{From_Date} : new java.util.Date(2010,01,01)

default value of  $P{To_Date} : new java.util.Date(2016,11,11)

#example 2        

$P{From_Date} : 1.1.2013

$P{To_Date} : 11.11.2016

QUERY:

select

tbl1.cantReqNo as OrderNumb, tbl2.empname as Employee,tbl1.canteen as ServicePlace, convert(datetime,tbl1.serviceDate,103) as ServiceDate,tbl3.itemName as FoodItem,  tbl1.totalAmt as Cost,tbl1.status as Status,tbl4.deptname as Department

from

canteenRequisition tbl1 , employee tbl2,canteenRequstionDetails tbl3, department tbl4

where

tbl1.empNo=tbl2.empno and tbl1.cantReqNo=tbl3.cantReqNo and tbl4.Deptid=tbl1.createdDept

 and tbl1.empNo=200045  and replace(tbl1.canteen,' ','')= 'sitecanteen'

and tbl1.serviceDate > $P{From_Date} and tbl1.serviceDate < $P{To_Date}

group by

tbl1.cantReqNo, tbl2.empname ,tbl1.canteen ,tbl1.serviceDate,

tbl3.itemName ,  tbl1.totalAmt,tbl1.status,tbl4.deptname

 

 

[joseng62]

If using relative dates 

Then create parameter in jasperstudio example 
Name: "datefrom"
Class: net.sf.jasperreports.types.date.DateRange
Prompting: you decide
Default value: new net.sf.jasperreports.types.date.DateRangeBuilder("MONTH-1").toDateRange()
Explained "MONTH-1", you can use DAY, WEEK, YEAR.

Then create second parameter in jasperstudio for the query. 
Example
Name: "FROM_DATE"
Class: java.util.Date
Prompting:
Default value: $P{datefrom}.getEnd()

Follow same steps for to date
Query Example:

select "columns" from table where "datefield" between  $P{FROM_DATE} and $P{DATE_TO} 

For none relative dates
Only create 2 parameters, TO and FROM DATES
Example
Name: "FROM_DATE"
Class: java.util.Date
Prompting: you decide
Default value: new Date((new Date()).getTime()-7*24*60*60*1000)    
You can set the time period for now date just do new Date()

If you have an issue with the date format, then either format the date field sql side to be same as jasper side or format jasper parameter to meet sql fields date format. 

If you just want to hardcode test. Then default string value should work. 
"2016-01-01" set paramter as string type.