compare Timestamp value from DB2

[vivien siu]

Hello, this is the logic I need to code:

1. A method get yesterday's date which will return me a Java.Util.Date object.
2. get a timestamp value from DB2 table.
3. get the date value only from the timestamp.
4. Then, use the Java.Util.Date object to compare the Timestamp's date value I get from DB2 table.

my question is I don't know how to compare a Date object with a Timestamp value from DB2. What is DB2's built-in method to get date only from timestamp?

my code:

 
public Date getYesterday(){
        Calendar cal = Calendar.getInstance();  //gets current date
        cal.add(Calendar.DATE, -1);             //changes current date to yesterday date
        System.out.println(cal.toString());
        Date dt = cal.getTime();                //creates a Date obj using yesterday's date
        return dt;
    }
 
Date stmtDate = mailStatement.getYesterday();
        mailStatement.getEmailAdd(stmtDate);
 
public void getEmailAdd(Date sstmtdate){
        Timestamp dstmtdate = new Timestamp(sstmtdate.getTime());
        int subscriber_id;
        String subject = null;
        String emailBody = null;
        String emailTemplate = null;
        String emailAdd = null;
        
       // System.out.println(dstmtdate.toString());
        /* gets each active subsriber from db */
        String sql_getSubscriber = "select A.SUBSCRIBER_ID, A.ACCOUNT_NO, A.LNAME, C.SSTMTTYPE, C.DSTMTDATE, C.SFILEPATHENCRYPTED " +
                "from TBL_SUBSCRIBER A INNER JOIN TBL_STATEMENT C ON A.ACCOUNT_NO = C.ACCOUNT_NO WHERE A.IS_ACTIVE = 1 AND C.DSTMTDATE = " + dstmtdate + "";

this is the error I getting:
com.ibm.db2.jcc.b.SQLException: An unexpected token "11" was found following "TMTDATE = 2006-03-15". Expected tokens may include: "OR".

[Adeel Ansari]

Dont go for any DB specific methods. I suggest you to do this with your code. You can use java.text.SimpleDateFormat class to format the date and get only the date part into a util.Date object from Timestamp object. Now you can are having 2 util.Date objects. No more problems.

You can also work with Calendar.

[Edited to make it more clear]
[ March 16, 2006: Message edited by: Adeel Ansari ]

[vivien siu]

this is what I used instead:

select A.SUBSCRIBER_ID, A.ACCOUNT_NO, A.LNAME, C.SSTMTTYPE, C.DSTMTDATE, C.SFILEPATHENCRYPTED " +
"from TBL_SUBSCRIBER A inner join TBL_STATEMENT C ON A.ACCOUNT_NO = C.ACCOUNT_NO WHERE A.IS_ACTIVE = '1' " +
"AND C.DSTMTDATE = cast('" + yesterday + "' as timestamp)

will try to use the Date(db2 timestamp) method also because the time part is useless to me

thank you!

[Reid M. Pinchback]

I think you are making your life harder than need be. Don't even try to serialize the date as a string. Just use a prepared statement and bind the date as a parameter. Date serialization into literals is often touchy to get a database to accept, and a real nightmare if you have to support more than one database vendor.

[Adeel Ansari]

Originally posted by Reid M. Pinchback:
I think you are making your life harder than need be. Don't even try to serialize the date as a string. Just use a prepared statement and bind the date as a parameter. Date serialization into literals is often touchy to get a database to accept, and a real nightmare if you have to support more than one database vendor.

Cent percent agreement. See this.