一个语句备份

try {
sql = "select a.tr_trnbr,a.tr_part,a.tr_rev,a.tr_custitmcode,a.tr_stockcode,left(CONVERT(varchar(12),a.tr_date, 121 ),10) as date,DATEDIFF(day,a.tr_date,'"+stoptime+"') as day,a.tr_nbr,isnull(cast(a.tr_unitweight as numeric(10,4)),0) as unitweight";
sql += ",isnull(cast(a.localprice as numeric(10,4)),0) as price ,cast(isnull(a.tr_qty_loc,0)-(select isnull(sum(isnull(b.sws_qty_ship,0)),0) from tsout_det b(nolock) ,tsout_mstr c(nolock)";
sql += "where b.tr_trnbr=a.tr_trnbr and b.sout_nbr=c.sout_nbr and c.sout_date < '"
+ stoptime + " 23:59:59' and c.sout_status='2') as numeric(14,0)) as balance ,";
sql += "cast(0 as numeric(14,2)) as totalweight ,";
sql += "cast(0 as numeric(14,2)) as totalprice,cast(''as varchar(255)) as date30a,cast(''as varchar(255)) as date30b,cast(''as varchar(255)) as date30c,cast(''as varchar(255)) as date60a,cast(''as varchar(255)) as date60b,cast(''as varchar(255)) as date60c,cast(''as varchar(255)) as date90a,cast(''as varchar(255)) as date90b,cast(''as varchar(255)) as date90c,cast(''as varchar(255)) as date120a,cast(''as varchar(255)) as date120b,cast(''as varchar(255)) as date120c,cast(''as varchar(255)) as date150a,cast(''as varchar(255)) as date150b,cast(''as varchar(255)) as date150c,cast(''as varchar(255)) as date180a,cast(''as varchar(255)) as date180b,cast(''as varchar(255)) as date180c,cast(''as varchar(255)) as date210a,cast(''as varchar(255)) as date210b,cast(''as varchar(255)) as date210c,cast(''as varchar(255)) as date240a,cast(''as varchar(255)) as date240b,cast(''as varchar(255)) as date240c,cast(''as varchar(255)) as date270a,cast(''as varchar(255)) as date270b,cast(''as varchar(255)) as date270c,cast(''as varchar(255)) as date300a,cast(''as varchar(255)) as date300b,cast(''as varchar(255)) as date300c,cast(''as varchar(255)) as date330a,cast(''as varchar(255)) as date330b,cast(''as varchar(255)) as date330c,cast(''as varchar(255)) as date360a,cast(''as varchar(255)) as date360b,cast(''as varchar(255)) as date360c,cast(''as varchar(255)) as date370a,cast(''as varchar(255)) as date370b,cast(''as varchar(255)) as date370c into #tem_hist  from ttr_hist as a(nolock)";
sql += " where 1=1 and a.tr_type<>'STOCK_OUT' and (tr_balance>0 or (tr_balance=0 and stampdatetime>'"+stoptime+" 23:59:59')) and tr_eff_date<='"+stoptime+" 23:59:59'";
if (!stocknamefilt.equals("")) {
sql += " and a.tr_stockcode='" + stocknamefilt + "'";
}
if (!stocknbr.equals("")) {
sql += " and a.tr_nbr like '%" + stocknbr + "%'";
}
if (!productno1.equals("")&&!productno2.equals("")) {
sql += " and a.tr_part between '" + productno1 + "' and '"+productno2+"'";
}
stmt.executeUpdate(sql);
sql="update #tem_hist set totalweight=balance*unitweight,totalprice=balance*price ";
stmt.executeUpdate(sql);
sql="update #tem_hist set date30a=balance,date30b=totalweight,date30c=totalprice where day between 0 and 30";
stmt.executeUpdate(sql);
sql="update #tem_hist set date60a=balance,date60b=totalweight,date60c=totalprice where day between 31 and 60";
stmt.executeUpdate(sql);
sql="update #tem_hist set date90a=balance,date90b=totalweight,date90c=totalprice where day between 61 and 90";
stmt.executeUpdate(sql);
sql="update #tem_hist set date120a=balance,date120b=totalweight,date120c=totalprice where day between 91 and 120";
stmt.executeUpdate(sql);
sql="update #tem_hist set date150a=balance,date150b=totalweight,date150c=totalprice where day between 121 and 150";
stmt.executeUpdate(sql);
sql="update #tem_hist set date180a=balance,date180b=totalweight,date180c=totalprice where day between 151 and 180";
stmt.executeUpdate(sql);
sql="update #tem_hist set date210a=balance,date210b=totalweight,date210c=totalprice where day between 181 and 210";
stmt.executeUpdate(sql);
sql="update #tem_hist set date240a=balance,date240b=totalweight,date240c=totalprice where day between 211 and 240";
stmt.executeUpdate(sql);
sql="update #tem_hist set date270a=balance,date270b=totalweight,date270c=totalprice where day between 241 and 270";
stmt.executeUpdate(sql);
sql="update #tem_hist set date300a=balance,date300b=totalweight,date300c=totalprice where day between 271 and 300";
stmt.executeUpdate(sql);
sql="update #tem_hist set date330a=balance,date330b=totalweight,date330c=totalprice where day between 301 and 330";
stmt.executeUpdate(sql);
sql="update #tem_hist set date360a=balance,date360b=totalweight,date360c=totalprice where day between 331 and 360";
stmt.executeUpdate(sql);
sql="update #tem_hist set date370a=balance,date370b=totalweight,date370c=totalprice where day > 360";
stmt.executeUpdate(sql);
} catch (SQLException e) {
out.print("Excepton:" + e.getMessage());
}



1.cast(0 as numeric(14,2)) as totalweight 占位
2.left(CONVERT(varchar(12),a.tr_date, 121 ),10) as date 时间格式化2009-10-23
3.DATEDIFF(day,a.tr_date,'"+stoptime+"') as day   时间差（多少天）