select rbillEntry.FActualAmt as rbillEntry,
customer.FNumber as Fnumber,orgCompany.Fnumber as orgCompanyFnumber --实收金额 客户
from T_CAS_ReceivingBillEntry rbillEntry --实收金额
left join T_CAS_ReceivingBill rbill
on (rbillEntry.FReceivingBillID = rbill.FID and rbill.CFActuallyArriveTime is not null)
left join t_Org_Company orgCompany on orgCompany.Fid = rbill.Fcompanyid
left join T_BD_Customer customer
on rbill.FPayerID = customer.FID
left join T_BD_BizAnaylysisCode bizSis
on customer.FBizAnalysisCodeID = bizSis.fid
where (rbill.CFActuallyArriveTime <= TO_DATE(substr('2012-02-29',1,10), 'YYYY-MM-DD') and
rbill.CFActuallyArriveTime >= TO_DATE(substr('2011-03-01',1,10), 'YYYY-MM-DD'))
and rbill.FBillStatus = 14 --14已收款
and (bizSis.Fnumber in ('01.00316') or 0 is null)
and (orgCompany.fnumber in ('999.0056') or 0 is null)
and rbill.FSourceType='100
这个语句为什么不能取出2012-02-29日的数据。而日前改为2012-03-01就可以取出。
执行结果:
1 1280000.0000 01.00316 999.0056 2011-12-07
2 800000.0000 01.00316 999.0056 2011-12-15
3 500000.0000 01.00316 999.0056 2011-12-23
4 1000000.0000 01.00316 999.0056 2012-02-29
customer.FNumber as Fnumber,orgCompany.Fnumber as orgCompanyFnumber --实收金额 客户
from T_CAS_ReceivingBillEntry rbillEntry --实收金额
left join T_CAS_ReceivingBill rbill
on (rbillEntry.FReceivingBillID = rbill.FID and rbill.CFActuallyArriveTime is not null)
left join t_Org_Company orgCompany on orgCompany.Fid = rbill.Fcompanyid
left join T_BD_Customer customer
on rbill.FPayerID = customer.FID
left join T_BD_BizAnaylysisCode bizSis
on customer.FBizAnalysisCodeID = bizSis.fid
where (rbill.CFActuallyArriveTime <= TO_DATE(substr('2012-02-29',1,10), 'YYYY-MM-DD') and
rbill.CFActuallyArriveTime >= TO_DATE(substr('2011-03-01',1,10), 'YYYY-MM-DD'))
and rbill.FBillStatus = 14 --14已收款
and (bizSis.Fnumber in ('01.00316') or 0 is null)
and (orgCompany.fnumber in ('999.0056') or 0 is null)
and rbill.FSourceType='100
这个语句为什么不能取出2012-02-29日的数据。而日前改为2012-03-01就可以取出。
执行结果:
1 1280000.0000 01.00316 999.0056 2011-12-07
2 800000.0000 01.00316 999.0056 2011-12-15
3 500000.0000 01.00316 999.0056 2011-12-23
4 1000000.0000 01.00316 999.0056 2012-02-29
rbill.CFActuallyArriveTime >= TO_DATE('2011-03-01', 'YYYY-MM-DD'))
rbill.CFActuallyArriveTime <= TO_DATE(substr('2011-03-01',1,10), 'YYYY-MM-DD'))
select rbillEntry.FActualAmt as rbillEntry,
customer.FNumber as Fnumber,orgCompany.Fnumber as orgCompanyFnumber ,to_char(rbill.CFActuallyArriveTime,'YYYY-MM-DD') as CFActuallyArriveTime --实收金额 客户
from T_CAS_ReceivingBillEntry rbillEntry --实收金额
left join T_CAS_ReceivingBill rbill
on (rbillEntry.FReceivingBillID = rbill.FID and rbill.CFActuallyArriveTime is not null)
left join t_Org_Company orgCompany on orgCompany.Fid = rbill.Fcompanyid
left join T_BD_Customer customer
on rbill.FPayerID = customer.FID
left join T_BD_BizAnaylysisCode bizSis
on customer.FBizAnalysisCodeID = bizSis.fid
where (rbill.CFActuallyArriveTime <= TO_DATE('2012-02-29', 'YYYY-MM-DD') and
rbill.CFActuallyArriveTime >= TO_DATE('2011-03-01','YYYY-MM-DD'))
and rbill.FBillStatus = 14 --14已收款
and (bizSis.Fnumber in ('01.00316') or 0 is null)
and (orgCompany.fnumber in ('999.0056') or 0 is null)
and rbill.FSourceType='100
我改为between and 也查不出来
rbill.CFActuallyArriveTime >= TO_DATE('2011-03-01', 'YYYY-MM-DD'))
rbill.CFActuallyArriveTime <= TO_DATE('2011-03-01','YYYY-MM-DD')
rbill.CFActuallyArriveTime >= TO_DATE('2012-02-29', 'YYYY-MM-DD') and
rbill.CFActuallyArriveTime <= TO_DATE('2012-03-01','YYYY-MM-DD')
select to_char(CFActuallyArriveTime,'yyyymmdd hh24:mi:ss')
from T_CAS_ReceivingBill rbill
where rbill.CFActuallyArriveTime >= TO_DATE('2012-02-29', 'YYYY-MM-DD')
and rbill.CFActuallyArriveTime <= TO_DATE('2012-03-01','YYYY-MM-DD')