使用文本输入搜索介于/和之间的访问日期列 [英] using text input to search Access date column with between/and

问题描述

我有一个需要2个日期的表格.

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.

将文本解析为日期:

<%
  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查询:

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?

我应该将java.util.Date转换为java.sql.Date吗?

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

我应该使用BETWEEN/AND而不是<和>?

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

推荐答案

您的查询肯定无法正常工作，因为您最终将执行如下所示的查询:

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.

当然取决于您的约会日期.如果您仅使用java.sql包，则可以实际使用PreparedStatement.

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();
            }

您的endDate和startDate应该是java.sql.Date的实例.

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

这篇关于使用文本输入搜索介于/和之间的访问日期列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！