Solved

SQL problems.. ">" and "<"

Posted on 2003-11-25
53
420 Views
Last Modified: 2010-04-01
I am using Ms Access for this project.
I have this raw data in the table;
14/01/2003
12/02/2003
12/06/2003
12/09/2003

And this is SQL Statement.
SELECT * FROM PURCHASE WHERE DateA>'14/01/2003';

And guest what will come out?
It's NOTHING!!

if i change the sql statement to, startDate<'14/01/2003'; this is the data come out.
12/09/2003
12/06/2003
12/02/2003

WHat is going on? I am loss... please help.

If my statement is like this,
SELECT *
FROM purchase
WHERE DateA>'14/01/2003';

The result should be,
12/02/2003
12/06/2003
12/09/2003

and if

SELECT *
FROM purchase
WHERE DateA<'14/01/2003';
thereshould be no result at all.

But that's not what i am getting... please point my error.



0
Comment
Question by:chainreaction
  • 19
  • 18
  • 7
  • +2
53 Comments
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9823152
You are using date field as a string!!! ( Or database has it defined as String)
WHERE DateA<'14/01/2003' (See these quotes they make it behave like a string...
so string wise
14 is > 12.....
thats why your first query will not return anything and because of same reason second query will return whatever is less than "14" ...

Hope this helps...
0
 

Author Comment

by:chainreaction
ID: 9823254
Kuldeep, you are right. In my DB schema i define aas a Stirng...

So, you mean the way i should code is like this,

SELECT * FROM PURCHASE WHERE DateA>14/01/2003;

Let me try and get back to you.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9823345
Use a preparedstatement...

PreparedStatement ps = connection.prepareStatement( "SELECT * FROM PURCHASE WHERE DateA < ?" ) ;
SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
ps.setDate( 1, sdf.parse( "14/01/2003" ) ) ;

then execute the ps like you would a normal statement, and get the ResultSet back :-)

Tim
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 14

Expert Comment

by:kennethxu
ID: 9826552
Tim is correct that you should use prepared statement assuming that the column type in MS Access is date type.

but the last like of Tim's code need a little fix in order get it compiled:

PreparedStatement ps = connection.prepareStatement( "SELECT * FROM PURCHASE WHERE DateA < ?" ) ;
SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
ps.setDate( 1, new java.sql.Date( sdf.parse( "14/01/2003" ).getTime() ) );
ResultSet rs=ps.executeQuery();

0
 
LVL 35

Expert Comment

by:TimYates
ID: 9826880
oh yeah :-)

whoops!
0
 
LVL 3

Expert Comment

by:applekanna
ID: 9828164
>>In my DB schema i define aas a Stirng...

Shouldnt he have to change the design to a date/datetime object first to do date operations.
I have tried this with mySql and it did not work.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9828692
yes applekana, u r right, I guess Tim and I overlooked it. date value should be always kept in a date type column.
0
 

Author Comment

by:chainreaction
ID: 9828792
Dear all,

in MsAccess, the Date type got couples of way. But none of them suit my need. I wish to set my Date type as dd/MM/yyyy. But all the date type in the Ms Access is none of this format. That's why i choose to set is as a String.

Anyway, i've changed the type to Date, a general format. MM/DD/YYYY (as default by MsAccess).

This is my statement (runing in the Access Query Enginee). Again, this is raw data:
14/01/2003
12/02/2003
12/06/2003
12/09/2003

but the db change the data to this format;
ExpDateA
1/14/2003
12/2/2003
12/6/2003
12/9/2003
11/26/2004

so when i run the query, in the db,

SELECT ExpDate
FROM purchase
WHERE ExpDateA>11/15/2003;

This come out.
ExpDate
26/11/2003
14/11/2003
22/11/2003
05/02/2003
26/11/2004

Mean incorrect right?

And i follow yours instruction, change the type to default MsAccess Date fromat and run my preparedstatemtn in the jsp page.

    PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE ExpDate > ?" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
    ps.setDate( 1, new java.sql.Date( sdf.parse( "15/11/2003" ).getTime() ) );
     rs=ps.executeQuery();
int counter=0;
while (rs.next()){
    counter++;
    out.println("<BR>");
    out.println(counter + ":"+ sdf.format(rs.getDate("ExpDateA")) );

This is the result...

1:09/12/2003

2:14/01/2003

3:06/12/2003

4:26/11/2004

The line number 2 is incorrect. its should be there...

0
 
LVL 3

Expert Comment

by:applekanna
ID: 9828903
>in MsAccess, the Date type got couples of way.

I do not think you need to worry about this as date is just a relative field, it ialuwas stored as long I guess and returned in the partifular format and locae you want it to be and you can see that y this is being done
sdf.parse( "15/11/2003" ).getTime()

AFAIK with MSAccess to query on a date field  we use
select * from Login where  lastDate = #11/15/2003#

I shall check it in my comp and while you can read these threads
http://www.devarticles.com/forum/t3698/s8b0980a7ea9e670ee7b893e710899d09.html
:)


0
 
LVL 3

Expert Comment

by:applekanna
ID: 9828905
try this

SELECT ExpDate
FROM purchase
WHERE ExpDateA>#11/15/2003#;
0
 
LVL 3

Expert Comment

by:applekanna
ID: 9828915
This is the ofrmat u visually see the dates in Access
14/01/2003
12/02/2003
12/06/2003
12/09/2003

since you have used this format u get result as

SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
you get this format
1/14/2003
12/2/2003
12/6/2003
12/9/2003
11/26/2004


also u have to query the db using the mm/dd/yyyy format
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9828922
>> But all the date type in the Ms Access is none of this format. That's why i choose to set is as a String.
there is no format of a date type, the format you see the what one of why the client display it. MSAccess itself it a database and also a client. But in reality, that 2 part are independent.

When you use jdbc access to MSAccess database, you are not touching the client part at all. so whatever displayed on MSAccess doesn't matter at all.

Always use Date type for you date value. there is NO FORMAT associated with your data.
0
 

Author Comment

by:chainreaction
ID: 9828954
ken, OK.

If i've change the data type to date. this is my code.

  PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE ExpDate > ?" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
    ps.setDate( 1, new java.sql.Date( sdf.parse( "15/11/2003" ).getTime() ) );
     rs=ps.executeQuery();
int counter=0;
while (rs.next()){
    counter++;
    out.println("<BR>");
    out.println(counter + ":"+ sdf.format(rs.getDate("ExpDateA")) );
    out.println("<BR>");

This is the raw data. (Please remember this is MM/dd/yyyy format)

ExpDateA
1/14/2003
12/2/2003
12/6/2003
12/9/2003
11/26/2004

and this is the result page after i run the jsp page.

1:09/12/2003

2:14/01/2003

3:06/12/2003

4:26/11/2004

The #2 shouldnt come out right?
0
 
LVL 3

Expert Comment

by:applekanna
ID: 9829001
can u try this and tell the results
Statement ps = conn.createStatement( "SELECT * FROM PURCHASE WHERE ExpDate > #11-15-2003#" ) ;
 rs=ps.executeQuery();
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829035
you are searching by ExpDate, but display ExpDateA can you please test this two and compare the result:

==================== display all ===================
  PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
    // ps.setDate( 1, new java.sql.Date( sdf.parse( "15/11/2003" ).getTime() ) );
     rs=ps.executeQuery();
int counter=0;
while (rs.next()){
    counter++;
    out.println("<BR>");
    out.println(counter + ":"+ sdf.format(rs.getDate("ExpDate")) );
    out.println("<BR>");

===================== earch by > =====================
  PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE ExpDate > ?" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
    ps.setDate( 1, new java.sql.Date( sdf.parse( "15/11/2003" ).getTime() ) );
     rs=ps.executeQuery();
int counter=0;
while (rs.next()){
    counter++;
    out.println("<BR>");
    out.println(counter + ":"+ sdf.format(rs.getDate("ExpDate")) );
    out.println("<BR>");
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829050
BTW, you can also easily change the display format of date/time column.
select table, click design button, select the date/time column and set the format under field properties.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829053
I meant change display format in MSAccess.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829063
applekanna, you are correct on using #11/15/2003# in MSAccess. personally, I would prefer to use prepared statement so that my code can also run on other database.
0
 

Author Comment

by:chainreaction
ID: 9829069
Ken, yeah you are right. My problem.

This is the first result. After i have change to ExpDateA.

1:09/12/2003

2:14/01/2003

3:06/12/2003

4:02/12/2003

5:26/11/2004


and this is part 2. where i add in >15/11/2003.
1:09/12/2003

2:06/12/2003

3:02/12/2003

4:26/11/2004

Ken, ok. What is the format i should pass in if my db's date format is MM/dd/yyyyy into my sql statement if I make the date "new java.sql.Date( sdf.parse( "15/11/2003" ).getTime() )" as a variable?? Should i set the imcoming format to "MM/dd/yyyy" or "dd/MM/yyyy"??
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829079
>> What is the format i should pass in if my db's date format is MM/dd/yyyyy

In database, date data type is store as long integer. as I said, there is NO FORMAT associated with data of date type.

The format is only what you see when it's been displayed. And a date type data can be display as ANY format you like.
0
 
LVL 3

Expert Comment

by:applekanna
ID: 9829080
thank q kenneth ... EE is a great place to learn :)

0
 

Author Comment

by:chainreaction
ID: 9829103
ken, this is the 'dd/MM/yyyy' format which is the same as my incoming startdate format.
and the result is correct. I mean the retrieved data.

<%
String startdate=request.getParameter("startdate");
String enddate=request.getParameter("enddate");

Connection conn=null;
ResultSet rs=null;
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   conn = DriverManager.getConnection("jdbc:odbc:myDSN");
    PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE ExpDateA > ?" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
    ps.setDate( 1, new java.sql.Date( sdf.parse( startdate ).getTime() ) );
     rs=ps.executeQuery();
int counter=0;
while (rs.next()){
    counter++;
    out.println("<BR>");
    out.println(counter + ":"+ sdf.format(rs.getDate("ExpDateA")) );
    out.println("<BR>");
}

**********
but when i change the date format. to "MM/dd/yyyy" which is to suit the DB's format (MM/dd/yyyy), the result is incorrect.

String startdate=request.getParameter("startdate");
String enddate=request.getParameter("enddate");
Connection conn=null;
ResultSet rs=null;
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   conn = DriverManager.getConnection("jdbc:odbc:myDSN");
    PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE ExpDateA > ?" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "MM/dd/yyyy" ) ;
    ps.setDate( 1, new java.sql.Date( sdf.parse( startdate ).getTime() ) );
     rs=ps.executeQuery();
int counter=0;
while (rs.next()){
    counter++;
    out.println("<BR>");
    out.println(counter + ":"+ sdf.format(rs.getDate("ExpDateA")) );
    out.println("<BR>");
}


Mean the date format should be the input base?
0
 

Author Comment

by:chainreaction
ID: 9829120
forget to paste the results.
assume the startdate is 15/11/2003

1st set:
1:09/12/2003

2:06/12/2003

3:02/12/2003

4:26/11/2004

2nd set:
1:11/26/2004


0
 
LVL 3

Expert Comment

by:applekanna
ID: 9829135
>>String startdate=request.getParameter("startdate");
String enddate=request.getParameter("enddate");


The format in which u get the dates should also change accordingly
I mean startDate = dd/mm/yyyy

you should send in as mm/dd/yyyy from the earlier page
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829136
chainreaction, I cannot emphasis more that
THERE IS NO FORMAT IN DATABASE FOR DATE TYPE.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829142
OK, let's put this way. when exactly format you want to use?
0
 

Author Comment

by:chainreaction
ID: 9829203
ken, in my user entry page, i want to use dd/MM/yyyy.

But in my db, my date format is MM/dd/yyyyy

So,
1. What format i should use if i am insert, or updating a date into the db?
2. On my normal query, what format i should use?
   ( >>THERE IS NO FORMAT IN DATABASE FOR DATE TYPE: Maybe the asnwer is no specific format, any type of date format is workable"??)
0
 

Author Comment

by:chainreaction
ID: 9829231
tested this and doesn't work.

  SimpleDateFormat smf = new SimpleDateFormat("MM/dd/yyyy");
         
  pstmt=conn.prepareStatement("insert into purchase values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            pstmt.setString(1, customer );
            pstmt.setString(2, warranty );
            pstmt.setString(3, model);
            pstmt.setDate(4, new java.sql.Date(smf.format( insdateA )) );
            pstmt.setDate(5, new java.sql.Date(smf.format(expdateA)) );
            pstmt.setString(6, insdateB );

expdateA and insdateA is a Date format in my db,.
it's prompted me,

beans/purchase.java [90:1] cannot resolve symbol
symbol  : constructor Date (java.lang.String)
location: class java.sql.Date
            pstmt.setDate(4, new java.sql.Date(smf.format( insdateA )) );
                             ^

0
 

Author Comment

by:chainreaction
ID: 9829301
11/26/2003 06:38PM PST message was sovled after  i change the code to this.

  pstmt.setDate(4, new java.sql.Date(smf.parse( insdateA ).getTime()) );
  pstmt.setDate(5, new java.sql.Date(smf.parse(expdateA).getTime()) );
         
and the data can be insert into the DB.

Ken, do you agree;
1. What format i should use if i am insert, or updating a date into the db?
ans: should be the datatype of date in the db.

2. On my normal query, what format i should use?
ans: same as above. the date format should be the same as in DB's date format.

0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829323
no! the answer IS
THERE IS NO FORMAT IN DATABASE FOR DATE TYPE
and not no specific format.
MSAccess = Database (JET Engine) + Application Development Tool (VBA)
the "format" you see is from the Application Development Tool, there is nothing to do with database.

In JET Engine, date/time is store as double value.
(please do me a favor, open MS Excel, enter "11/26/2003  10:13:00 PM", menu Format->Cells, select number tab, select Number in Category listbox and click OK, you'll see 37951.93 , that's the value actually been stored for that date)

So, in you jsp coding, please forget about the "format" in your database. thank you.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9829380
OK, before I give up, let's give another try:

1. create a table called mytable and make mydate as only column, the data type should be date/time

then let's do this:

String strDateA = "11/26/2003";
String strDateB = "26/11/2003";

SimpleDateFormat formatA= new SimpleDateFormat("MM/dd/yyyy");
SimpleDateFormat formatB= new SimpleDateFormat("dd/MM/yyyy");

java.util.Date dateA = formatA.parse( strDateA );
java.util.Date dateB = formatB.parse( strDateB );

out.println( "Is dataA and dataB the same: " + dateA.equals( dateB ) + "<p>" );

Connection conn=null;
ResultSet rs=null;
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   conn = DriverManager.getConnection("jdbc:odbc:myDSN");
  // let's insert 2 records
  PreparedStatement  ps=conn.prepareStatement("insert into mytable values(?)");
            ps.setDate( 1, new java.sql.Date( dataA ) );
            ps.executeUpdate();
            ps.setDate( 1, new java.sql.Date( dateB ) );
            ps.executeUpdate();
  // then, read them all
  ps=conn.prepareStatement("select *from mytable");
  rs=ps.executeQuery();
while (rs.next()){
    java.sql.Date mydate = rs.getDate("mydate");
    out.println("<BR>");
    out.println( formatA.format( mydate )  + " / " + formatB.format( mydate ) + " / " + mydate );
}

0
 

Author Comment

by:chainreaction
ID: 9829738
ken, i have change the code. The insert part from
           ps.setDate( 1, new java.sql.Date( dataA ) );
           ps.executeUpdate();
           ps.setDate( 1, new java.sql.Date( dateB ) );
           ps.executeUpdate();
to

           ps.setDate( 1, new java.sql.Date( dateA.getTime() ) );
           ps.executeUpdate();
           ps.setDate( 1, new java.sql.Date( dateB.getTime() ) );
           ps.executeUpdate();

And i get this information..

Is dataA and dataB the same: true

11/26/2003 / 26/11/2003 / 2003-11-26
11/26/2003 / 26/11/2003 / 2003-11-26
11/26/2003 / 26/11/2003 / 2003-11-26
11/26/2003 / 26/11/2003 / 2003-11-26



THANK YOU very much for your information. it's clear my doubts... Thousand tq.. and apologize....:-)
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9829751
Seems while I was sleeping you guys had a very good and informational discussion......

great going EE keep it up.....:-)
0
 

Author Comment

by:chainreaction
ID: 9829831
ken, but this doesn't work...

String fodate="25/11/2003";
...
..
java.util.Date fdate =  formatA.parse(fodate);
.
.
.
ps=conn.prepareStatement("select * from mytable where mydate > fdate");
 rs=ps.executeQuery();
while (rs.next()){
   java.sql.Date mydate = rs.getDate("mydate");
   out.println("<BR>");
   out.println( formatA.format( mydate )  + " / " + formatB.format( mydate ) + " / " + mydate );
}


The result is this only...
Is dataA and dataB the same: true

*no additional date come out... why?
raw data:
mydate
11/26/2003
11/26/2003
11/26/2003
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9830273
Its the same mistake again... In the query use preparedstatement and use setDate() function to set your date in query....
That should work
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9830374
isn't that what I said in post #3?
0
 

Author Comment

by:chainreaction
ID: 9830672
TimYates, Kuldeep,
isnt this is the same like yours comments?


String fodate="25/11/2003";
...
..
java.util.Date fdate =  formatA.parse(fodate);

I've alrefady parse the date before i passed it in...... :-(
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9830789
Its true that you are parsed it before but then you are doing
conn.prepareStatement("select * from mytable where mydate > fdate"); ( see the qry is a string so how will the system know what the heck you mean by 'fdate'??? it will be treated as literal......)
now even if you change it to

conn.prepareStatement("select * from mytable where mydate > "+ fdate);
then also its a string concatination and hence fdate will again get converted to a String internally.... and will not give you right results...

so the right way of doing it will be...  
conn.prepareStatement("select * from mytable where mydate > ?");
and then setDate(1,fdate);

Hope I am making sense here....:-)
0
 

Author Comment

by:chainreaction
ID: 9830792
ok ok.. this is my page after i amended...

Raw Data
InsDateA
11/27/2003
10/1/2003

 conn = DriverManager.getConnection("jdbc:odbc:myDSN");
    PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE (ExpDateA > ? AND ExpDateA < ?)" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
    ps.setDate( 1, new java.sql.Date( sdf.parse( startdate ).getTime() ) );
    ps.setDate( 2, new java.sql.Date( sdf.parse( enddate ).getTime() ) );
     rs=ps.executeQuery();
int counter=0;
while (rs.next()){
    counter++;
    out.println("<BR>");
out.println(counter + ":"+ rs.getString("Customer") + sdf.format(rs.getDate("ExpDateA")) );
    out.println("<BR>");
}

if my startdate is 1/11/2003 and enddate is 30/11/2003, still no data come out....
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9830817
Would you try this same page after removing getTime() from your dates please?? for some reason I am not liking those functions....
0
 

Author Comment

by:chainreaction
ID: 9831211
done. but got error after i have remove the getTime()

result_0005ffilter_0005fa$jsp.java [109:1] cannot resolve symbol
symbol  : constructor Date (java.util.Date)
location: class java.sql.Date
                    ps.setDate( 1, new java.sql.Date( sdf.parse( startdate )) );
                                   ^
result_0005ffilter_0005fa$jsp.java [110:1] cannot resolve symbol
symbol  : constructor Date (java.util.Date)
location: class java.sql.Date
                   ps.setDate( 2, new java.sql.Date( sdf.parse( enddate )) );
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9831219
>> if my startdate is 1/11/2003 and enddate is 30/11/2003, still no data come out....

1. is data type of ExpDataA column date/time?

2. can you try this for testing make sure the start and end date is correct:

startdate = "1/11/2003 ";
enddate = "30/11/2003";
 conn = DriverManager.getConnection("jdbc:odbc:myDSN");
    PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE (ExpDateA > ? AND ExpDateA < ?)" ) ;
    SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
    ps.setDate( 1, new java.sql.Date( sdf.parse( startdate ).getTime() ) );
    ps.setDate( 2, new java.sql.Date( sdf.parse( enddate ).getTime() ) );
     rs=ps.executeQuery();
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9831259
>> Would you try this same page after removing getTime() from your dates please??
Kuldeepchaturvedi, you need it to get a java.sql.Date from java.util.Date. otherwise compile time error.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9831273
Oooops missed that sql.Date Vs util.Date stuff......... Grinning..:-)
I have done that in past as well.......... Guess now on I will keep it in mind..
0
 

Author Comment

by:chainreaction
ID: 9831297
ken, kludeep, please stay with me.. i am testing one by one...
0
 

Author Comment

by:chainreaction
ID: 9831310
ken, hard-coded it as

****
//String startdate=request.getParameter("startdate");
//String enddate=request.getParameter("enddate");
String startdate = "1/11/2003";
String enddate = "30/11/2003";
%>
Start Date : <%= startdate %>
End Date : <%= enddate %>
<br>
<%
Connection conn=null;
ResultSet rs=null;
SimpleDateFormat sdf = new SimpleDateFormat( "MM/dd/yyyy" ) ;
SimpleDateFormat vf = new SimpleDateFormat("dd/MM/yyyy");
   
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   conn = DriverManager.getConnection("jdbc:odbc:myDSN");
   PreparedStatement ps = conn.prepareStatement( "SELECT * FROM PURCHASE WHERE " +
    " ExpDateA > ? And ExpDateA < ? " ) ;  
 
    ps.setDate( 1, new java.sql.Date( sdf.parse(startdate).getTime()));
   ps.setDate( 2, new java.sql.Date( sdf.parse(enddate ).getTime()));

******

but the result still come out like this.

Start Date : 1/11/2003 End Date : 30/11/2003

1:AGNES TAN27/11/2004

2:KIMURA20/11/2003

3:KAWASKI24/11/2003

4:NAKATA18/12/2003
0
 

Author Comment

by:chainreaction
ID: 9831328
confirmed it;s defined as Date/Time format.
and this is the raw dta

ExpDateA
11/27/2004
11/26/2005
11/20/2003
12/18/2003
11/24/2003
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9831402
>>     ps.setDate( 1, new java.sql.Date( sdf.parse(startdate).getTime()));
>>   ps.setDate( 2, new java.sql.Date( sdf.parse(enddate ).getTime()));

the format must compliant with the String that you are formating! it should be:

    ps.setDate( 1, new java.sql.Date( vf.parse(startdate).getTime()));
   ps.setDate( 2, new java.sql.Date( vf.parse(enddate ).getTime()));

as I told you, forget about the "format" in database, only stay with the format you use in JSP.
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 135 total points
ID: 9831428
if you try to print out this, you'll see what's wrong:
Start Date: <%=sdf.parse(startdate)%><p>
End Date: <%=sdf.parse(enddate )%><p>
0
 

Author Comment

by:chainreaction
ID: 9831470
Start Date : Sat Jan 11 00:00:00 SGT 2003 End Date : Sat Jun 11 00:00:00 SGT 2005

1:KIMURA20/11/2003

2:KAWASKI24/11/2003

No wonder...............

Tq again ken....

i don't know how can i thank you back.. :-(

0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9831502
Don't worry, I'll fly to Singapore some day and make sure you still remember that date type in database actuall has no format ;-)
0
 

Author Comment

by:chainreaction
ID: 9831572
friend... i am in malaysia... makesure you drop by here...
and give me a call.....
03-78446155....
ha....
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9831801
Oh, I thought it was Singapore because I saw your time zone is SGT.
I had been there for almost 6 years and have a lot of friends in Singapore and Malaysia. Now I got one more ;-)

happy coding and good luck.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article outlines why you need to choose a backup solution that protects your entire environment – including your VMware ESXi and Microsoft Hyper-V virtualization hosts – not just your virtual machines.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question