Jasper report - how to pass date parameter as "day", "month", "year" part from java to jasper report using SQL

1
votes

I want to pass "month", "year" parameters from java to jasper report using SQL. I have already hard coded the day value. I just want to join the day, month and year parameter as a date string, but I am getting error

java.sql.SQLDataException: ORA-01843: not a valid month

Below is my code:

WHERE TO_DATE(START_DATE,'DD-MM-YYYY HH:MI:SS')>=  ADD_MONTHS( TO_DATE('26-' || UPPER($P{MONTH}) || '-' ||  $P{YEAR} ,'DD-MM-YYYY') ,-1)
AND 
TO_DATE(END_DATE,'DD-MM-YYYY HH:MI:SS') <=  ADD_MONTHS( TO_DATE('26-' || UPPER($P{MONTH})|| '-' ||  $P{YEAR} ,'DD-MM-YYYY') ,0)
1
sqloraclejasper-reports
DATE_ADD ( NOW ( ) , INTERVAL 1 MONTH ) ? try this – Mani Deep
I want to know whether this parameter concatenation is correct or not. "TO_DATE('26-' || UPPER($P{MONTH}) || '-' || $P{YEAR} ,'DD-MM-YYYY')" – Ashwini
You should tag oracle also. Concatenation looks fine, although UPPER is useless. Are you passing a two digits month (with leading zero) string? – Sal
Iam passing month like this "Jun". Three letter month word – Ashwini
I would suggest you to copy your SQL and paste it in the text field so that you can check SQL and verify date format. – Fahad Anjum

1 Answers

0
votes

Use following format: DD-MMM-YYYY.