7 Replies Latest reply: Oct 23, 2008 12:59 AM by 807589 RSS

    How to convert String to date ? - MS access

    807589
      I want to convert a string "10-10-08" [ MM-dd-yy ] into date type in same format to use that in query statement [MS Access].
      In table , I am using field called "date" with "MM-dd-yy" format.

      Example:
      <%@ page import= "java.sql.*"%>
      <%@ page import="java.text.SimpleDateFormat"%>
      <%@ page import="java.util.Date"%>
      <%
      String d="10-9-08";

           SimpleDateFormat dateFormat = new SimpleDateFormat("MM-dd-yy");
                Date date = dateFormat.parse(d);
                
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           Connection con = DriverManager.getConnection("jdbc:odbc:bspipdb");
           Statement st = con.createStatement();
      ResultSet rs=st.executeQuery("Select * from datacollectdb where date='"+date+"'");
      %>

      I am getting "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
      " error.

      How to use the string "10-10-08" in where statement?
        • 1. Re: How to convert String to date ? - MS access
          796254
          sangee wrote:
          I want to convert a string "10-10-08" [ MM-dd-yy ] into date type in same format to use that in query statement [MS Access].
          In table , I am using field called "date" with "MM-dd-yy" format.
          I think a column named "date" is a very bad idea. Not just because "date" is a keyword. It's not very self-describing. How about "birth_date" or "hire_date" or "went_on_my_first_date"?
          Example:
          Scriptlets? There's your second mistake.
          <%@ page import= "java.sql.*"%>
          <%@ page import="java.text.SimpleDateFormat"%>
          <%@ page import="java.util.Date"%>
          <%
          String d="10-9-08";
          The way you're doing things, "2-40-99" is a valid date. Is that what you want?
               SimpleDateFormat dateFormat = new SimpleDateFormat("MM-dd-yy");
                    Date date = dateFormat.parse(d);
                    
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
               Connection con = DriverManager.getConnection("jdbc:odbc:bspipdb");
          Wrong again. Better to use a PreparedStatement and bind that Date.
               Statement st = con.createStatement();
          ResultSet rs=st.executeQuery("Select * from datacollectdb where date='"+date+"'");
          %>

          I am getting "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
          " error.

          How to use the string "10-10-08" in where statement?
          Use PreparedStatement and bind.

          Your JSP should not be getting a database connection. Pool it.

          Your code is wrong on every level. You don't close resources, you don't separate you app into layers, your database code is not in a POJO. I'd recommend that you throw this away and start again.

          %
          • 2. Re: How to convert String to date ? - MS access
            807589
            Hi,
            I coded this one to learn about date function.So I didnt use connection pool.
            I changed date field to cdate.
            I am getting error " java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. "

            How to correct this error?

            <%@ page import= "java.sql.*"%>
            <%@ page import="java.text.SimpleDateFormat"%>
            <%@ page import="java.util.Date"%>
            <%@ page import= "java.io.IOException"%>
            <%
            String d="10-9-08";

                 SimpleDateFormat dateFormat = new SimpleDateFormat("MM-dd-yy");
                      Date date = dateFormat.parse(d);
                      
            try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                 Connection con = DriverManager.getConnection("jdbc:odbc:bspipdb");
                 PreparedStatement st = con.prepareStatement("Select * from datacollectdb where cdate='"+date+"'");
            ResultSet rs=st.executeQuery();
            while(rs.next())
            {
            out.print(rs.getDate(2));
            }
            rs.close();
            st.close();
            con.close();
            }
            catch(Exception e)
            {out.print(e);}

            %>
            • 3. Re: How to convert String to date ? - MS access
              807589
              You try using the date in the String format itself.
              try without parsing it.
              The database driver will take care of converting it to date format.
              ("Select * from datacollectdb where date="+{color:#ff0000}d{color}+"")
              • 4. Re: How to convert String to date ? - MS access
                807589
                i m facing also this type of problem my code is
                public String getFineAmount()throws ServletException,IOException{
                try
                {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                Connection con=DriverManager.getConnection("jdbc:odbc:db2");
                Statement st=con.createStatement();
                String sql = "select to_days(curdate())-to_days(dates) from db2 where studentname='"+sname+"' and bookname='"+bname+"'";
                System.out.println(sql);
                ResultSet rs=st.executeQuery(sql);
                if(rs.next())
                { d=rs.getInt(1);}
                
                if(d>60)
                {t=d-60;
                return(Integer.toString(t*2));
                }
                else
                {return "null your book hasbeen sumbited on time";
                }
                }
                
                catch(Exception e)
                { 
                e.printStackTrace();
                return "failed";
                }
                }
                i m always geeting output failed .i m using ms access as database .so plz help me
                • 5. Re: How to convert String to date ? - MS access
                  807589
                  bhavishya wrote:
                  i m facing also this type of problem
                  Your problem has no relation the OP's problem.

                  Start a new thread in the correct forum. The correct forum would be the JDBC forum. And do try and avoid thread hijacking in future please. Thanks.
                  • 6. Re: How to convert String to date ? - MS access
                    807589
                    sangee wrote:
                    Hi,
                    I coded this one to learn about date function.So I didnt use connection pool.
                    I changed date field to cdate.
                    I am getting error " java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. "
                    You didn't use PreparedStatements properly. That was your error. In your SQL you should leave the date value as a parameter (aka ?) then you will call setDate(1, yourDateValue) on the prepared statement instance before you execute it.

                    Try reading the basic JDBC tutorial. [_Using PreparedStatements_|http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html]
                    • 7. Re: How to convert String to date ? - MS access
                      807589
                      __nibu_issac wrote:
                      You try using the date in the String format itself.
                      try without parsing it.
                      The database driver will take care of converting it to date format.
                      ("Select * from datacollectdb where date="+{color:#ff0000}d{color}+"")
                      What turnip truck did you fall off of?

                      You should be reading the JDBC tutorial as well. Start from the beginning [_JDBC Tutorial_|http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html]