Problem with date

[pradeep84]

Hi .. friends.. in the below program

 public void actionPerformed(ActionEvent ae)
{
 int flag=0;
 s1=(from.getText());
 s2=(to.getText());
 if(ae.getSource()==view)
{
    
     
try 
{
   
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con=DriverManager.getConnection("jdbc:odbc:prism");
      stmt=con.createStatement();
      stmt1=con.createStatement();
     
      rs=stmt.executeQuery("select SUM(NOSRECEIVED),SUM(REJPCS) from Visual where DATE BETWEEN '"+s1+"' AND '"+s2+"' ");
      rs1=stmt1.executeQuery("select SUM(D1),SUM(D2),SUM(D3),SUM(D4),SUM(D5),SUM(D6),SUM(D7),SUM(D8),SUM(D9),SUM(D10),SUM(D11),SUM(D12),SUM(D13) FROM Rej where DATE BETWEEN'"+s1+"'AND '"+s2+"'");
     
     
      
 }

where s1,s2 are String Data Types
in the above program i got the sql error as Data type mismatch criteria.. i know that the problem is with data types..can u say wat kind of data type i have to declare to solve this problem...

Thanking you....



Regards
Pradeep

[BigDaddyLH]

Date can be tricky but there is a simple rule: always use PreparedStateme nt; never use just Statement: http://java.sun.com/docs/books/tutor.../prepared.html

[r035198x]

Hi .. friends.. in the below program

 public void actionPerformed(ActionEvent ae)
{
 int flag=0;
 s1=(from.getText());
 s2=(to.getText());
 if(ae.getSource()==view)
{
    
     
try 
{
   
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con=DriverManager.getConnection("jdbc:odbc:prism");
      stmt=con.createStatement();
      stmt1=con.createStatement();
     
      rs=stmt.executeQuery("select SUM(NOSRECEIVED),SUM(REJPCS) from Visual where DATE BETWEEN '"+s1+"' AND '"+s2+"' ");
      rs1=stmt1.executeQuery("select SUM(D1),SUM(D2),SUM(D3),SUM(D4),SUM(D5),SUM(D6),SUM(D7),SUM(D8),SUM(D9),SUM(D10),SUM(D11),SUM(D12),SUM(D13) FROM Rej where DATE BETWEEN'"+s1+"'AND '"+s2+"'");
     
     
      
 }

where s1,s2 are String Data Types
in the above program i got the sql error as Data type mismatch criteria.. i know that the problem is with data types..can u say wat kind of data type i have to declare to solve this problem...

Thanking you....



Regards
Pradeep

What database are you using? Checks its specs for a to_date(it could be called something else depending on the database you are using) function that converts characters to dates.

[BigDaddyLH]

What database are you using? Checks its specs for a to_date(it could be called something else depending on the database you are using) function that converts characters to dates.

No, it's still preferable to use a PreparedStateme nt and let the driver worry about that. The resulting code will be more vendor-independent.

[r035198x]

No, it's still preferable to use a PreparedStateme nt and let the driver worry about that. ....

I didn't dispute that. Using the PreparedStateme nt makes him convert the string to date in the code (which is a good idea) and then use setDate or setTimestamp. It won't automatically convert his strings to dates for camparisions with the database values.

[BigDaddyLH]

I didn't dispute that. Using the PreparedStateme nt makes him convert the string to date in the code (which is a good idea) and then use setDate or setTimestamp. It won't automatically convert his strings to dates for camparisions with the database values.

I agree. He should be working with a Date object, not a string. That calls for SimpleDateForma t, if starting from a string:

http://java.sun.com/javase/6/docs/api/java/text/SimpleDateFormat.html