How to Compare date in Prepared statement

Question

I have Sybase Statement which is mentioned below :

 select s.airbill_nbr, s.orig, s.dest,p.swa_acct_nbr, s.prod_type, s.outbnd_date, s.shppr_name, s.consgn_name,s.shipping_chrg, s.misc_chrg, s.decl_val_chrg, s.tax_amt, s.fuel_chrg,  s.scrty_chrg, s.tot_chrgs, s.tot_pcs, s.tot_chrgbl_wt, s.tot_actl_wt from shipment s, shipment_outbnd_stn o, payment p
    where s.sa_shpmt_nbr = o.sa_shpmt_nbr 
    and o.sa_shpmt_nbr = p.sa_shpmt_nbr
    and o.outbnd_date=convert(date,'01/29/2015',101)
    and s.outbnd_empl_id is not null
    and p.fop_cd = 'SW'
    and p.swa_acct_nbr =40584
    and s.void_flag is null

I have to create a prepared statement for the above code in Java and I have created the below One :

    String variable1="29/01/2015";
                    
 psRetrieveAwbData = cattsDbc.prepareStatement( "select s.airbill_nbr, s.orig, s.dest, " +
         "p.swa_acct_nbr, s.prod_type, s.outbnd_date, s.shppr_name, s.consgn_name, " +
         "s.shipping_chrg, s.misc_chrg, s.decl_val_chrg, s.tax_amt, s.fuel_chrg, " +
         "s.scrty_chrg, s.tot_chrgs, s.tot_pcs, s.tot_chrgbl_wt, s.tot_actl_wt " +
         "from shipment s, shipment_outbnd_stn o, payment p " +
         "where s.sa_shpmt_nbr = o.sa_shpmt_nbr " +
         "and o.sa_shpmt_nbr = p.sa_shpmt_nbr " +
         "and o.outbnd_date = '"+ variable1 +"'" +
        "and s.outbnd_empl_id is not null " +
        "and p.fop_cd = 'SW' " +
        "and p.swa_acct_nbr = " + iAccount +
        "and s.void_flag is null" );

but I dont know how to put date in date Format because the way I have put it is not working by taking the variable1, as variable1 It is taking as String and not finding out the Solution.Could you please tell me how to create the prepared statement where I can directly put date or put the date by storing it in some variable.

See http://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement — racraman, Feb 20 '15 at 07:28
I have seen that and implemented String variable1="29/01/2015"; SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); java.sql.Date d=(Date) format.parse(variable1); but How to put d variable in statement , I dont know. — gaganshu sharma, Feb 20 '15 at 07:30

score 0 · Answer 1 · answered Feb 20 '15 at 07:55

PreparedStatement pstmt = con.prepareStatement("select s.airbill_nbr, s.orig, s.dest, " +
                        "p.swa_acct_nbr, s.prod_type, s.outbnd_date, s.shppr_name, s.consgn_name, " +
                        "s.shipping_chrg, s.misc_chrg, s.decl_val_chrg, s.tax_amt, s.fuel_chrg, " +
                        "s.scrty_chrg, s.tot_chrgs, s.tot_pcs, s.tot_chrgbl_wt, s.tot_actl_wt " +
                        "from shipment s, shipment_outbnd_stn o, payment p " +
                        "where s.sa_shpmt_nbr = o.sa_shpmt_nbr " +
                        "and o.sa_shpmt_nbr = p.sa_shpmt_nbr " +
                        "and o.outbnd_date = ?" +
                        "and s.outbnd_empl_id is not null " +
                        "and p.fop_cd = 'SW' " +
                        "and p.swa_acct_nbr = " + iAccount +
                        "and s.void_flag is null" );");

   pstmt.setDate (dt);

How to Compare date in Prepared statement

1 Answers1