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

浏览数：23 / 时间：2015年06月12日

今天刚做了一个异常数据显示报表，可是编辑好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 ;