Solved

SQL problems.. ">" and "<"

Posted on 2003-11-25
53
412 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
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
Comment Utility
oh yeah :-)

whoops!
0
 
LVL 3

Expert Comment

by:applekanna
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
try this

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

Expert Comment

by:applekanna
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I meant change display format in MSAccess.
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
thank q kenneth ... EE is a great place to learn :)

0
 

Author Comment

by:chainreaction
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
chainreaction, I cannot emphasis more that
THERE IS NO FORMAT IN DATABASE FOR DATE TYPE.
0
 
LVL 14

Expert Comment

by:kennethxu
Comment Utility
OK, let's put this way. when exactly format you want to use?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:chainreaction
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
isn't that what I said in post #3?
0
 

Author Comment

by:chainreaction
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
ken, kludeep, please stay with me.. i am testing one by one...
0
 

Author Comment

by:chainreaction
Comment Utility
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
Comment Utility
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
Comment Utility
>>     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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now