案例学习Oracle：细节很重要

　　问：以下是我写的SQL语句,有如下问题解决不了:

　　问题一:运行时出错，弹出error: (full) year must be between -4713 and +9999, and not be 0

　　请问oracle的日期时间转换到底是怎么回事，不是很明白??在这个SQL语句里，应该怎么写?

　　问题二:上面的SQL语句是通过substr(a.FCALLERID,1,7)=b.PREFIX这句话关联起来的，这是手机号的字段名，2张表唯一能够关联的就是通过这个字段，但采集的数据不全，a表中还有未采集到的数据，请问怎样才能将所有的数据都采集出来?

select thedate as 日期,
       count(distinct phonenum) as 电话号码数,
       count(times) as 拨打总次数,
       sum(feecount) as 总计时长,
       sum(sumfee) as 营业额,
       city as 地区
 from (select to_date('a.FSTARTDATE','yyyymmdd') as thedate,
              a.FCALLERID as phonenum,a.FCALLEDID as times,
              a.FSPENTTIME as feecount,
              a.FTOTALFEE as sumfee,
              b.CITY as city from T_IVR_CALLERRECORD a,
              T_IVR_SMSPREFIX b where a.FSTARTDATE＞='20051129'
              AND FENDDATE＜'20051130'
              AND substr(a.FCALLERID,1,7)=b.PREFIX)
 group by city

　　答：1)a表的FSTARTDATE 字段是什么数据类型， 估计是 date

　　SELECT to_date('a.FSTARTDATE', 'yyyymmdd') AS thedate,这句就有问题了，为什么要加单引号

　　2)a表中还有未采集到的数据，可以使用外关联

　　3)条件 a.fstartdate ＞= '20051129' AND fenddate ＜ '20051130' 应该放在 AND substr(a.fcallerid, 1, 7) = b.prefix 后面

　　-- 修改后

SELECT thedate AS 日期,
       COUNT(DISTINCT phonenum) AS 电话号码数,
       COUNT(times) AS 拨打总次数,
       SUM(feecount) AS 总计时长,
       SUM(sumfee) AS 营业额,
       city AS 地区
  FROM (SELECT trunc(a.fstartdate,'dd') AS thedate,  --日期
               a.fcallerid AS phonenum,
               a.fcalledid AS times,
               a.fspenttime AS feecount,
               a.ftotalfee AS sumfee,
               b.city AS city
          FROM t_ivr_callerrecord a, t_ivr_smsprefix b
         WHERE substr(a.fcallerid, 1, 7) = b.prefix(+)
           AND a.fstartdate ＞= to_date('20051129','YYYY-MM-DD')
           AND fenddate ＜ to_date('20051130','YYYY-MM-DD') + 1
       )
 GROUP BY city