oracle数据库查询中错误--格式化出现两次

今天刚做了一个异常数据显示报表，可是编辑好sql执行时出现错误：格式化出现错误，

下面是我的代码

select bmg.material_group_code as vehicleCode,
       colInfo.lotId as lotId, 
       info.erp_product_id as productCode, 
       info.erp_product_name as productName,
       colInfo.startDeviceName as startDeviceName,
       colInfo.endDeviceName as endDeviceName,
       colInfo.startDeviceCollectionDate as startDeviceCollectionDate,
       colInfo.endDeviceCollectionDate as endDeviceCollectionDate
from  (select trac.lot_id as lotId,
             max(decode(dev.gid,'40288b8f3c8486e4013c8980a8f401cc',dev.description)) as startDeviceName,
             max(decode(dev.gid,'40288b8f3c8486e4013c8980a8f401cc',trac.collection_time)) as startDeviceCollectionDate,
             max(decode(dev.gid,'40288b8f3c8486e4013c897eb77501c7',dev.description)) as endDeviceName,
             max(decode(dev.gid,'40288b8f3c8486e4013c897eb77501c7',trac.collection_time)) as endDeviceCollectionDate
        from pro_vehicle_traking trac left join ins_device dev on trac.device_gid = dev.gid
       where trac.collection_time between to_date('2014-02-10','yyyy-MM-dd hh:mm:ss')
       and to_date('2014-03-10','yyyy-MM-dd hh:mm:ss')
        group by trac.lot_id
       ) colInfo
left join pro_item_with_lot lot on colInfo.lotId = lot.lot_id
left join pro_order pro on pro.gid = lot.order_gid
left join mtb_vehicle_info info on info.gid = pro.vehicle_info_gid
left join binf_material_group bmg on bmg.material_group_id = info.special_car_type_gid
left join mtb_material_group_class mmgc on mmgc.gid = bmg.material_group_type_gid
where mmgc.material_group_class_id ='CXZ'
and  colInfo.startDeviceCollectionDate is null
or colInfo.endDeviceCollectionDate < colInfo.startDeviceCollectionDate
;

其中如果将15行和16行的语句去除则执行正常，经过查找资料，找到错误原因所在

经常处理日期的的java程序员可能都会将日期转换的格式与oracle特有的搞混淆，其中java的一般为yyyy-MM-dd hh:mm:ss,而Oracle数据库中则以

yyyy-mm-dd hh24:mi:ss来表示相应的格式，所以将上述的错误改变一下就可以执行正常了。

正确的sql语句如下

select bmg.material_group_code as vehicleCode,
       colInfo.lotId as lotId, 
       info.erp_product_id as productCode, 
       info.erp_product_name as productName,
       colInfo.startDeviceName as startDeviceName,
       colInfo.endDeviceName as endDeviceName,
       colInfo.startDeviceCollectionDate as startDeviceCollectionDate,
       colInfo.endDeviceCollectionDate as endDeviceCollectionDate
from  (select trac.lot_id as lotId,
             max(decode(dev.gid,'40288b8f3c8486e4013c8980a8f401cc',dev.description)) as startDeviceName,
             max(decode(dev.gid,'40288b8f3c8486e4013c8980a8f401cc',trac.collection_time)) as startDeviceCollectionDate,
             max(decode(dev.gid,'40288b8f3c8486e4013c897eb77501c7',dev.description)) as endDeviceName,
             max(decode(dev.gid,'40288b8f3c8486e4013c897eb77501c7',trac.collection_time)) as endDeviceCollectionDate
        from pro_vehicle_traking trac left join ins_device dev on trac.device_gid = dev.gid
       where trac.collection_time between to_date('2014-02-10','yyyy-MM-dd hh:mi:ss')
       and to_date('2014-03-10','yyyy-MM-dd hh:mi:ss')
        group by trac.lot_id
       ) colInfo
left join pro_item_with_lot lot on colInfo.lotId = lot.lot_id
left join pro_order pro on pro.gid = lot.order_gid
left join mtb_vehicle_info info on info.gid = pro.vehicle_info_gid
left join binf_material_group bmg on bmg.material_group_id = info.special_car_type_gid
left join mtb_material_group_class mmgc on mmgc.gid = bmg.material_group_type_gid
where mmgc.material_group_class_id ='CXZ'
and  colInfo.startDeviceCollectionDate is null
or colInfo.endDeviceCollectionDate < colInfo.startDeviceCollectionDate
;