Oracle-java How to fetch data between two Timestamps
hai...
I am using a program wherei have to get all the data entered into oracle db between two dates
(The first date being manually inputted and the second one is inputdate-24hrs) . The code is as follows....
Connection c =null;
PreparedStatement pStmt =null;
ResultSet rs =null;
try
{
//eg String dt=14/03/2007, hr=03, min=27, ampm=am
DateFormat dateFormat =new SimpleDateFormat("dd/MM/yyyy");
DateFormat outForm =new SimpleDateFormat("yyyy/MM/dd");
Calendar cl = Calendar.getInstance();
Date inDate =new Date();
inDate = dateFormat.parse(dt);
cl.setTimeInMillis(inDate.getTime());
cl.add(Calendar.DAY_OF_MONTH, -1);
String str1 = outForm.format(new Date(cl.getTimeInMillis()));
String str2 = outForm.format(dateFormat.parse(dt));
System.out.println("Changed date = "+str1);
System.out.println("Original date = "+str2);
String toStr = str2+" "+hr+":"+min+" "+ampm.toUpperCase();
String fromStr = str1+" "+hr+":"+min+" "+ampm.toUpperCase();
System.out.println("From date = "+fromStr);
System.out.println("To date = "+toStr);
// For example, if u r confused, From date = 2007/03/14 03:27 AM
//To date = 2007/03/13 03:27 AM
String sql ="SELECT TRANS_ID, TO_CHAR(CONCAT('Rs.',TRANS_VALUE)) AS TRANS_VALUE, " +
"TO_CHAR(TRANS_DATE,'DD/MM/YYYY HH:MM AM') AS TRANS_DATE, ACCOUNT_ID, " +
"TO_CHAR(CREATED_DATE,'DD/MM/YYYY HH:MM AM') AS CREATED_DATE, CREATED_BY, " +
"INSTR_D_BANK_CD FROM TRANS_TB WHERE " +
"ACCOUNT_ID=? AND TO_CHAR(TRANS_DATE,'YYYY/MM/DD HH:MM AM') " +
"BETWEEN ? AND ? ;
c=ds.getConnection();
pStmt = c.prepareStatement(sql);
pStmt.setString(1, accountId);
pStmt.setString(2, fromStr);
pStmt.setString(3, toStr);
rs = pStmt.executeQuery();
}
The problem is that i am not getting results as expected
Can anyone point out if there is something wrong in the logic or in the program....
