今天刚做了一个异常数据显示报表，可是编辑好sql执行时出现错误：格式化出现错误，
下面是我的代码
1 select bmg.material_group_code as vehicleCode, 2 colInfo.lotId as lotId, 3 info.erp_product_id as productCode, 4 info.erp_product_name as productName, 5 colInfo.startDeviceName as startDeviceName, 6 colInfo.endDeviceName as endDeviceName, 7 colInfo.startDeviceCollectionDate as startDeviceCollectionDate, 8 colInfo.endDeviceCollectionDate as endDeviceCollectionDate 9 from (select trac.lot_id as lotId, 10 max(decode(dev.gid,‘40288b8f3c8486e4013c8980a8f401cc‘,dev.description)) as startDeviceName, 11 max(decode(dev.gid,‘40288b8f3c8486e4013c8980a8f401cc‘,trac.collection_time)) as startDeviceCollectionDate, 12 max(decode(dev.gid,‘40288b8f3c8486e4013c897eb77501c7‘,dev.description)) as endDeviceName, 13 max(decode(dev.gid,‘40288b8f3c8486e4013c897eb77501c7‘,trac.collection_time)) as endDeviceCollectionDate 14 from pro_vehicle_traking trac left join ins_device dev on trac.device_gid = dev.gid 15 where trac.collection_time between to_date(‘2014-02-10‘,‘yyyy-MM-dd hh:mm:ss‘) 16 and to_date(‘2014-03-10‘,‘yyyy-MM-dd hh:mm:ss‘) 17 group by trac.lot_id 18 ) colInfo 19 left join pro_item_with_lot lot on colInfo.lotId = lot.lot_id 20 left join pro_order pro on pro.gid = lot.order_gid 21 left join mtb_vehicle_info info on info.gid = pro.vehicle_info_gid 22 left join binf_material_group bmg on bmg.material_group_id = info.special_car_type_gid 23 left join mtb_material_group_class mmgc on mmgc.gid = bmg.material_group_type_gid 24 where mmgc.material_group_class_id =‘CXZ‘ 25 and colInfo.startDeviceCollectionDate is null 26 or colInfo.endDeviceCollectionDate < colInfo.startDeviceCollectionDate 27 ;
其中如果将15行和16行的语句去除则执行正常，经过查找资料，找到错误原因所在
经常处理日期的的java程序员可能都会将日期转换的格式与oracle特有的搞混淆，其中java的一般为yyyy-MM-dd hh:mm:ss,而Oracle数据库中则以
yyyy-mm-dd hh24:mi:ss来表示相应的格式，所以将上述的错误改变一下就可以执行正常了。
正确的sql语句如下
1 select bmg.material_group_code as vehicleCode, 2 colInfo.lotId as lotId, 3 info.erp_product_id as productCode, 4 info.erp_product_name as productName, 5 colInfo.startDeviceName as startDeviceName, 6 colInfo.endDeviceName as endDeviceName, 7 colInfo.startDeviceCollectionDate as startDeviceCollectionDate, 8 colInfo.endDeviceCollectionDate as endDeviceCollectionDate 9 from (select trac.lot_id as lotId, 10 max(decode(dev.gid,‘40288b8f3c8486e4013c8980a8f401cc‘,dev.description)) as startDeviceName, 11 max(decode(dev.gid,‘40288b8f3c8486e4013c8980a8f401cc‘,trac.collection_time)) as startDeviceCollectionDate, 12 max(decode(dev.gid,‘40288b8f3c8486e4013c897eb77501c7‘,dev.description)) as endDeviceName, 13 max(decode(dev.gid,‘40288b8f3c8486e4013c897eb77501c7‘,trac.collection_time)) as endDeviceCollectionDate 14 from pro_vehicle_traking trac left join ins_device dev on trac.device_gid = dev.gid 15 where trac.collection_time between to_date(‘2014-02-10‘,‘yyyy-MM-dd hh:mi:ss‘) 16 and to_date(‘2014-03-10‘,‘yyyy-MM-dd hh:mi:ss‘) 17 group by trac.lot_id 18 ) colInfo 19 left join pro_item_with_lot lot on colInfo.lotId = lot.lot_id 20 left join pro_order pro on pro.gid = lot.order_gid 21 left join mtb_vehicle_info info on info.gid = pro.vehicle_info_gid 22 left join binf_material_group bmg on bmg.material_group_id = info.special_car_type_gid 23 left join mtb_material_group_class mmgc on mmgc.gid = bmg.material_group_type_gid 24 where mmgc.material_group_class_id =‘CXZ‘ 25 and colInfo.startDeviceCollectionDate is null 26 or colInfo.endDeviceCollectionDate < colInfo.startDeviceCollectionDate 27 ;