//如果为‘农行’则把交易查询时间往后延一小时,因为农行次日凌晨一点前交易算做昨日
//这里主要为了掌握 date类型到timestamp类型查到具体时间区期中的数据if(searchCondition.getBeginTime()!=null&&searchCondition.getEndTime()!=null){
hql.append("and( (E.DIRECTCODE='ABC' AND B.TRANSACTIONTIME BETWEEN "
+ " TO_TIMESTAMP('"+sdf.format(searchCondition.getBeginTime())+ " 01.00.00.00" +"','YYYY-MM-DD HH24.MI.SS.FF')"
//加一天
+ " AND TO_TIMESTAMP('"+sdf.format(DateUtil.addDates(searchCondition.getEndTime(), 1)) + " 00.59.59.99"+"','YYYY-MM-DD HH24.MI.SS.FF')) "
+ " or ( E.DIRECTCODE!='ABC' AND B.TRANSACTIONTIME BETWEEN "
+ " TO_TIMESTAMP('"+sdf.format(searchCondition.getBeginTime())+ " 00.00.00.00" +"','YYYY-MM-DD HH24.MI.SS.FF') AND "
+ " TO_TIMESTAMP('"+sdf.format(searchCondition.getEndTime()) + " 23.59.59.99"+"','YYYY-MM-DD HH24.MI.SS.FF') ) )");
}
//oracle数据库端
and (
(E.DIRECTCODE='ABC' AND B.TRANSACTIONTIME BETWEEN
//往后加一小时,它会自己加上去 这里为了掌握oracle端使用numtodsinterval函数加一小时
TO_TIMESTAMP('2017-12-03 00.00.00.00', 'YYYY-MM-DD HH24.MI.SS.FF')+numtodsinterval(1,'hour') AND
TO_TIMESTAMP('2017-12-03 23.59.59.99', 'YYYY-MM-DD HH24.MI.SS.FF')+numtodsinterval(1,'hour')
)
or
(
//非农行
E.DIRECTCODE!='ABC'AND B.TRANSACTIONTIME BETWEEN
TO_TIMESTAMP('2017-12-03 00.00.00.00', 'YYYY-MM-DD HH24.MI.SS.FF') AND
TO_TIMESTAMP('2017-12-03 23.59.59.99', 'YYYY-MM-DD HH24.MI.SS.FF')
)
)