startdate and enddate not working when there is a year change

Question

I used a datepicker for startdate and end date which is as below

       <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
          <script src="//code.jquery.com/jquery-1.10.2.js"></script>
          <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
          <link rel="stylesheet" href="/resources/demos/style.css">
          <script type="text/javascript">
          $(function () {
                $( "#startDate" ).datepicker({
                      changeMonth: true,
                      changeYear: true,
                      dateFormat: 'dd-mm-yy'
                    });

                $( "#endDate" ).datepicker({
                      changeMonth: true,
                      changeYear: true,
                      dateFormat: 'dd-mm-yy'
                    });
            });
          </script>
         <style type="text/css">
                .search_textbx
        {
         background-image:url('/SalesPropeller/calendar.jpg');
            background-repeat:no-repeat;
           background-position:right;  

        }
                </style>
    <form name="form" action="quotationSearchResult.jsp" method="post">
     <input type="text" id="startDate" name="startDate" class="search_textbx" readonly="readonly" placeholder="&nbsp &nbsp dd-mm-yyyy"/> 
      <input type="text" id="endDate" name="endDate" class="search_textbx" readonly="readonly" placeholder="&nbsp &nbsp dd-mm-yyyy"/></td>
</form>

quotationSearchResult.jsp

<div id="divRight">
                &nbsp;
                <%
                    int count = 0;
                    int SlNo=1;
                %>
                <hr />
                <h3>Search Result for <u><%= request.getParameter("companyName")%></u> is : </h3>
                <hr/>
                <div id="divtable_wrapper">
                    <div id="divtbody" style="height: 470px;">
                        <form method="post" name="form">
                            <table class="divtable" width="1140">
                                <div id="divheader">
                                    <thead>
                                        <tr>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">Sl.NO</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">CompanyName</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">RefNo</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">Quot.Date</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">UserName</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">Grand Total</th>

                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">CST</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">VAT5.5</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">VAT14.5</th>
                                            <th style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">ServiceTax</th>


                                            <th colspan="1" style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;background: beige;">Action</th>
                                        </tr>
                                    </thead>
                                </div>

                                <%
                                    //Connection con = null;
                                    Object o2 = session.getAttribute("email");
                                    String email = o2.toString();
                                    String companyName = request.getParameter("companyName");
                                    String startDate=request.getParameter("startDate");

                                    String endDate=request.getParameter("endDate");
                                    int cId=0;
                                    //Statement st, st1;
                                    try {
                                        DBConnect db = new DBConnect();
                                        Connection con = db.getCon();

                                        Statement st = con.createStatement();
                                       // System.out.println("At SearchResult.jsp:" + email + "," + category + "," + search + "," + search_tf);
                                        ResultSet rs = st.executeQuery("Select CId,CompanyName,RefNo,Date,UserName,GrandTotal,CST,VAT5,VAT14,ServiceTax from marketing_database.quotationclient_details where companyName='"+companyName+"' AND Date BETWEEN '"+startDate+"' AND '"+endDate+"';");
                                       // String q2 = "update dummy set category='" + category + "' where id = '1';";
                                      //  st1.executeUpdate(q2);                    // To insert the category name to dummy table in database
%>

                                <%
                                    while (rs.next()) {
                                %>
                                <tr><td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td1"><%=SlNo%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td2"><%=rs.getString(2)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td3"><%=rs.getString(3)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td4"><%=rs.getString(4)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td5"><%=rs.getString(5)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td6"><%=rs.getString(6)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td7"><%=rs.getString(7)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td8"><%=rs.getString(8)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td9"><%=rs.getString(9)%></td>
                                    <td style="padding:1px 5px;border-right:1px solid #d47f66;border-bottom:1px solid #d47f66;" class="td10"><%=rs.getString(10)%></td>


                                    <td style="padding:1px 5px;border-bottom:1px solid #d47f66;" class="td12"><input type="button" name="edit" value="Report" style="background-color:#49743D;font-weight:bold;color:#ffffff;" onclick="generateReport(<%= rs.getString(1)%>);" ></td>

                                        <% count++;
                                        SlNo++;%>
                                </tr>
                                <%
                                    }
                                %>
                                <%
                                    } catch (Exception e) {
                                        e.printStackTrace();
                                    }
                                %>
                            </table>
                        </form>
                        <br>
                        <span style="float: left;"> &nbsp;<b><%= count%></b> Search Result Found.</span>
                <br><br>
                </div>
                </div>
                 </div>

When i give a startDate as "01-01-2016" and endDate as "31-01-2016" it is fetching the record as shown in the image But when i give startDate as "07-12-2015" and endDate as "18-01-2016" it is not fetching the record as shown in the image

So what's going and why it is not comparing the years properly.I am sql database as well

debug it, establish where the problem is. Are the String values correct `String startDate=request.getParameter("startDate");` ? — Scary Wombat, Jan 08 '16 at 07:48
maybe you don't have records between those date 07-12-2015 and 18-01-2016 — Abdelhak, Jan 08 '16 at 07:55
Take a look at post data in developer tools when you send a request and be sure what's being sent. — , Jan 08 '16 at 07:58
Yeah i tried what you said but it is not fetching the records — FIFA oneterahertz, Jan 08 '16 at 08:33
@PetterFriberg yeah i am using mysql and i took "Date" field as varchar(45) — FIFA oneterahertz, Jan 08 '16 at 09:19
The query needs to be formatted WHERE (DATE(date_field) BETWEEN '2015-12-07' AND '2016-01-31') — Petter Friberg, Jan 08 '16 at 09:22
Now it seems that you have it like WHERE (DATE(date_field) BETWEEN '07-12-2015' AND '31-01-201') — Petter Friberg, Jan 08 '16 at 09:23
@PetterFriberg yeah but my datatype for "Date" column is 'varchar' not 'DATE' — FIFA oneterahertz, Jan 08 '16 at 09:24
That was not a good idea... so it will sort on the basis of you varchar... and your date format is not in an sortable form.... — Petter Friberg, Jan 08 '16 at 09:26
Hmmm, probably we can convert your column to a date and then check it.... or do you wan't do re-design the database — Petter Friberg, Jan 08 '16 at 09:28
yeah but how to do. i mean i need the date format as dd-mm-yyyy but datatype "DATE" is taking as yyyy-mm-dd.So any conversion can happen so that it is stored in dd-mm-yyyy format. — FIFA oneterahertz, Jan 08 '16 at 09:28
Any change i dont mind but i need dd-mm-yyyy format in database — FIFA oneterahertz, Jan 08 '16 at 09:29
You should have Date as format in database then when you like to view it you format it as you like with SimpleDateFormat in java, pattern in jasper report ecc... However I have posted an answer.... My mission is to make you understand why you have the problem...... — Petter Friberg, Jan 08 '16 at 09:46
Note you can convert it database, to save data already stored, create a new column es. newDate with Date as format do an update on table newDate = STR_TO_DATE(`Date`, '%d/%m/%Y') then remove old column renaming the newDate column... anyway my pleasure.... have fun — Petter Friberg, Jan 08 '16 at 10:27

score 2 · Accepted Answer · edited May 23 '17 at 10:28

The problem is that the column "Date" is a varchar, the correct form in database is Date, furthermore "Date" is not a good name for a column, since its also an MySQL function.

Since the saved date format String is not in a sortable form, example on sorting your dates as String's:

07-01-2016
07-12-2015
18-01-2016

07-01-2016 is not between 07-12-2015 and 18-01-2016

The best solution is to convert column format to correct format Date in database.

A quick fix if this is not possible

We need to convert your String column to date and set correct formatting of your search string to mysql syntax

//convert search string to mysql format (note you can define variables for faster code and you should consider handling exceptions)
startDate = new SimpleDateFormat("yyyy-mm-dd").format(new SimpleDateFormat("dd/mm/yyyy").parse(startDate));
endDate = new SimpleDateFormat("yyyy-mm-dd").format(new SimpleDateFormat("dd/mm/yyyy").parse(endDate));

//syntax for query
... AND STR_TO_DATE(`Date`, '%d/%m/%Y') BETWEEN '"+startDate+"' AND '"+endDate+"';"

For additional information see

how to convert a string to date in mysql?

How do I query between two dates using MySQL?

You should also consider to use Prepared statement to avoid sql injection problems.

Avoiding SQL Injection

why is the "strDate" and "startDate" name given.they should be same or different?? — FIFA oneterahertz, Jan 12 '16 at 11:58
Actually i didnt apply the code of "startDate" and "endDate" so some dates are mismatching.Later when i read you answer carefully then i understood that those lines can change the scenario and it did.Thanks once again — FIFA oneterahertz, Jan 12 '16 at 12:09

startdate and enddate not working when there is a year change

1 Answers1