Excluding rows with NULL or empty fields 1

[gatetec]

I have this query to extract the rows that have invalid date data in them. This query pulls out all of rows with empty fields and/or NULL. How do you exclude those with empty fields and/or NULL so that the output contains the rows with wrong dates in them? If any of fields contains a wrong date, it needs to be shown on the output.

thx so much

select EMPID, LName, FName,
case when IsDate(GRADDATE) = 0 then GRADDATE else '' end,
case when IsDate(EMTEXP) = 0 then EMTEXP else '' end,
case when IsDate(AEMTEXP) = 0 then AEMTEXP else '' end,
case when IsDate(PAREXP) = 0 then PAREXP else '' end,
case when IsDate(ACLSEXP) = 0 then ACLSEXP else '' end,
case when IsDate(CPREXP) = 0 then CPREXP else '' end,
case when IsDate(BTLSEXP) = 0 then BTLSEXP else '' end,
case when IsDate(HEP1) = 0 then HEP1 else '' end,
case when IsDate(HEP2) = 0 then HEP2 else '' end,
case when IsDate(HEP3) = 0 then HEP3 else '' end,
case when IsDate(TBTEST) = 0 then TBTEST else '' end,
case when IsDate(ACLSIEXP) = 0 then ACLSIEXP else '' end,
case when IsDate(BTLSIEXP) = 0 then BTLSIEXP else '' end,
case when IsDate(PALSIEXP) = 0 then PALSIEXP else '' end,
case when IsDate(CPRIEXP) = 0 then CPRIEXP else '' end,
case when IsDate(EMTINSTEXP) = 0 then EMTINSTEXP else '' end
from EDPROF
where
--IsDate(DOB) = 0 OR
IsDate(GRADDATE) =0 OR
IsDate(EMTEXP) =0 OR
IsDate(AEMTEXP) =0 OR
IsDate(PAREXP) =0 OR
IsDate(ACLSEXP) =0 OR
IsDate(CPREXP) =0 OR
IsDate(BTLSEXP) =0 OR
IsDate(HEP1) =0 OR
IsDate(HEP2) =0 OR
IsDate(HEP3) =0 OR
IsDate(TBTEST) =0 OR
IsDate(ACLSIEXP) =0 OR
IsDate(BTLSIEXP) =0 OR
IsDate(PALSIEXP) =0 OR
IsDate(CPRIEXP) =0 OR
IsDate(EMTINSTEXP) =0

 

[RiverGuy]

That table looks like a real pain to work with. Such a tedious query. Anyways, you get the picture with the following code:

select EMPID, LName, FName,
         case when IsDate(GRADDATE) = 0 then GRADDATE else '' end,
    case when IsDate(EMTEXP) = 0 then EMTEXP else '' end,
    case when IsDate(AEMTEXP) = 0 then AEMTEXP else '' end,
           case when IsDate(PAREXP) = 0 then PAREXP else '' end,
    case when IsDate(ACLSEXP) = 0 then ACLSEXP else '' end,
    case when IsDate(CPREXP) = 0 then CPREXP else '' end,
    case when IsDate(BTLSEXP) = 0 then BTLSEXP else '' end,
           case when IsDate(HEP1) = 0 then HEP1 else '' end,
    case when IsDate(HEP2) = 0 then HEP2 else '' end,
    case when IsDate(HEP3) = 0 then HEP3 else '' end,
    case when IsDate(TBTEST) = 0 then TBTEST else '' end,  
    case when IsDate(ACLSIEXP) = 0 then ACLSIEXP else '' end,
           case when IsDate(BTLSIEXP) = 0 then BTLSIEXP else '' end,
    case when IsDate(PALSIEXP) = 0 then PALSIEXP else '' end,
    case when IsDate(CPRIEXP) = 0 then CPRIEXP else '' end,
    case when IsDate(EMTINSTEXP) = 0 then EMTINSTEXP else '' end
from EDPROF
where
 
(GRADDATE IS NOT NULL AND GRADDATE <> '' AND IsDate(GRADDATE) = 0) OR
(EMTEXP IS NOT NULL AND EMTEXP <> '' AND IsDate(EMTEXP) = 0) OR
...etc

 

[gatetec]

thx so much