Click here to Skip to main content
15,908,166 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to filter the values between two dates
in my data base it saves like 12 /July /2014 as a string, and when i tried to get the values from chosen dates to a iReport but it seems there is a problem with date format and in the report it shows all the values not just the chosen dates

This is the code i tried to filter the dates in iReport:

SQL
SELECT sent_by, sent_date, recive_date FROM evaluation WHERE decision='Approve' 
AND (recive_date BETWEEN $P{sdte} AND $P{edte})


and this is how i passed the 2 dates with parameters

Java
String strtdte = datechosser(jDateChooser2);

       String enddte = datechosser(jDateChooser1);
       String reportSource = "D:\\completePrintinReport.jrxml";
       Map<String, Object> params = new HashMap<String, Object>();

       params.put("sdte", strtdte);
       params.put("edte", enddte);
       JasperReport jasperReport = JasperCompileManager.compileReport(reportSource);

       Connection c = (Connection) ConnectionSet1.getInstance().getConnection();

       JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, params, c);
       JasperViewer.viewReport(jasperPrint, false);
       c.close();
Posted
Comments
Richard MacCutchan 15-Oct-14 3:40am    
in my data base it saves like 12 /July /2014 as a string
That is your first mistake. You should never uses strings to store dates, you should use the proper Date or DateTime data types.
gayan_priyankara 15-Oct-14 6:00am    
so what can i do to resolve this
Richard MacCutchan 15-Oct-14 6:11am    
Go and learn database design. If you store dates as strings in a database you will just get into more and more problems.

1 solution

You can change your table design to have the receive_date as datetime or date. Select the table and then right-click to go to design view. There you can change the datatype. Save it.

Else you can achieve it by using the below query

SQL
SELECT sent_by, sent_date, recive_date FROM evaluation WHERE decision='Approve'
AND (CAST(recive_date AS Date) BETWEEN $P{sdte} AND $P{edte})
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900