HREHR实施工具-06管理报表库-9.1日期型数据不合法.docVIP

9.1日期型数据不合法 业务场景 供系统管理员检查系统中所有日期型字段是否符合‘YYYY-MM-DD’格式，且内容符合日期型要求。 此类错误通常是由于导入人事子集时原始数据excel中即错误数据，因此建议导入数据之后使用该查询进行检查。 样表 表名 公司编码 公司名称 人员编码 人员姓名 字段名 字段内容 错误数据明细 人员任职信息 10001 宏力集团 HI0008 张倩 任职起始日期 0200-07-01 日期格式错误 由于一条查询语句只能查询一个字段，因此对于多个字段需要生成多条查询语句，并用 union 将其连接起来。 查询语句中用到了isdate函数，该函数在sql server 数据库中作为标准函数已经预置了，在oracle数据库中需要自行创建该函数，创建数据如下 报表制作 该报表采用手工sql制作 效果图 select 单位基本信息 as SetName,bd_corp.unitcode ,bd_corp.unitname, as KeyFldA, KeyFldB, as KeyFldC, as KeyFldD,成立日期 as FldName,bd_corp.createdate as WrongValue,日期格式错误 as WrongDetail, 1 as WrongCount from bd_corp where bd_corp.createdate is not null and 0 ISDATE bd_corp.createdate UNION select 单位基本信息 as SetName,bd_corp.unitcode ,bd_corp.unitname, as KeyFldA, KeyFldB, as KeyFldC, as KeyFldD,封存日期 as FldName,bd_corp.sealeddate as WrongValue,日期格式错误 as WrongDetail, 1 as WrongCount from bd_corp where bd_corp.sealeddate is not null and 0 ISDATE bd_corp.sealeddate UNION select 单位基本信息 as SetName,bd_corp.unitcode ,bd_corp.unitname, as KeyFldA, KeyFldB, as KeyFldC, as KeyFldD,营业或有效期限起始日期 as FldName,bd_corp.begindate as WrongValue,日期格式错误 as WrongDetail, 1 as WrongCount from bd_corp where bd_corp.begindate is not null and 0 ISDATE bd_corp.begindate UNION select 单位基本信息 as SetName,bd_corp.unitcode ,bd_corp.unitname, as KeyFldA, KeyFldB, as KeyFldC, as KeyFldD,营业或有效期限终止日期 as FldName,bd_corp.enddate as WrongValue,日期格式错误 as WrongDetail, 1 as WrongCount from bd_corp where bd_corp.enddate is not null and 0 ISDATE bd_corp.enddate UNION select 单位年审记录 as SetName,bd_corp.unitcode ,bd_corp.unitname, as KeyFldA, KeyFldB, as KeyFldC, as KeyFldD,审查日期 as FldName,om_corpexam.dexamdate as WrongValue,日期格式错误 as WrongDetail, 1 as WrongCount from bd_corp,om_corpexam where om_corpexam.dexamdate is not null and 0 ISDATE om_corpexam.dexamdate and bd_corp.pk_corp om_corpexam.pk_corp UNION select 部门基本信息 as SetName,bd_corp.unitcode ,bd_corp.unitname,bd_deptdoc.deptcode as KeyFldA,bd_deptdoc.deptname KeyFldB, as K