CODEWITHSUNDEEP
javasqlms-access
Cat
Cat

Reputation: 3

using text input to search Access date column with between/and

I have a form that will take 2 dates.

<form name="dateInput" action="dateResult.jsp" method="get">
<input type="text" name="startDate" />
<input type="text" name="endDate" />
<input type="submit" value="Submit" />
</form>

I want it to be able to filter and show me all records that have a date in between the two inputs on the results page.

Parsing the texts into dates: 

<%
  DateFormat formatter;
  formatter = new SimpleDateFormat("MM/dd/yyyy"); 

  String startDay; 
  startDay = request.getParameter("startDate");

  java.util.Date startingDay = formatter.parse(startDay);

  String endDay;
  endDay = request.getParameter("endDate");

  java.util.Date endingDay = formatter.parse(endDay);
%>

SQL Query: 

rs = stmt.executeQuery( "SELECT * FROM [Inventory Tracking] WHERE [Inventory Tracking].[Purchase_Date] => "+ startingDay +" AND < "+endingDay);

I've been messing around with it for a few hours and every change I make either returns a blank results page or an error page. What am I doing wrong?

Should I be converting the java.util.Date to java.sql.Date?

Should I use BETWEEN/AND instead of < and >'s?

Upvotes: 0

Views: 785

Answers (1)

jay c.
jay c.

Reputation: 1561

Your query definitely won't work since you will end up executing a query which will look like this:

SELECT * FROM [Inventory Tracking] WHERE [Inventory Tracking].[Purchase_Date] => Jul 25 15:00:40 EDT 2012 AND < Jul 25 15:00:40 EDT 2012.

Of course depending on your dates. If you're just using java.sql package, you can actually use PreparedStatement.

PreparedStatement ps = conn.prepareStatement("SELECT * FROM [Inventory Tracking] WHERE [Inventory Tracking].[Purchase_Date] => ? AND [Inventory Tracking].[Purchase_Date] < ?");
            try {
                ps.setDate(1, endDate);
                ps.setDate(2, start);
                ResultSet rs = ps.executeQuery();
                try {
                    while (rs.next()) {
                        // for each row logic
                    }
                } finally {
                    rs.close()
                }
            } finally {
                ps.close();
            }

Your endDate and startDate should be instance of java.sql.Date.

Upvotes: 1

Related Questions