SQL's ISDate() equivalent in AS400

Could you provide some example that will be likely to be input to the "DeliveryDate" column? In DB2, you may need to create you own function to validate if the data is a date, depend on what you usually have.

IF you have the privilege to create a function, take a look at this forum post here below:
http://www.code400.com/forum/showthread.php/2979-How-to-check-for-valid-date

The guy run into a similar issue like yours. you may just need to modify the function to your needs.

Hi Kent, what if the the column is in varchar and the date data is something like '1st June, 2011' or '01-06-2011', would that still work? very curious on this topic

Hi,

I am looking for comparision of datecolumn between a particular date.

If DateColumn1 is valid date and not null  and NOT ZERO then compare DateColumn1 with particular date range.

Otherwise check if DateColumn2 is valid date and (in case of DateColumn1 being not valid) and compare DateColumn2 with particular date range.

Thanks
Prahalad K

I Dont have previelge to declare a function, I have only previlage to query using OpenQuery() command and looking for solution using OpenQuery()

Hi Pralad,

If it's a varchar (or char) column that contains anything other than a date formatted as the DBMS expects, you'll have to convert it yourself.

Thanks

It seems this thread has ended without a solution - I'm interpreting that the author's need is the same as mine, currently:

I often have a flat file or intermediary table with columns of data in VARCHAR format.
The end goal of the organization is to insert these rows into a table that has properly defined columns - including dates.

My need is to validate that the data in the file is 100% valid before attempting the insert into a production database table.

Performing a simple insert into a formatted table in a test environment falls short - it will tell me if 100% of the dates are in date format (successful insert), but if not - all I'll know is I got an error.   I won't know if it's one or all of the dates, and I won't know which of the dates to look at.
Performing a CAST or CONVERT will similarly fail without identifying a row to look into.

Accordingly-
The author (and I) I believe are both seeking an equivalent to the ISDATE() function so that we can execute it on a set of data like this to validate it, and in the event of nonconformance, specifically identify the nonconformances (both scope and specific instances) to report back to the business to create awareness in a professional way, and communicate steps for correction that are thorough enough to succeed.

ISDATE() similar to SQL Server or Sybase would work...
A SYS_CALENDAR table like Teradata would work...

I'd believe IBM would have some method of achieving this functionality, right?

The functionality does not exist in DB2.  You'll have to test it yourself.  

The two approaches are to parse the string and test for validity, (which is not really something one wants to do), or to let DB2 attempt the conversion and trap failures.  The easiest way to do that is to write your own short function.

CREATE FUNCTION isdate (idate varchar(50))
RETURNS int
DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
BEGIN
  DECLARE EXIT HANDLER FOR SQL EXCEPTION return 0;
  RETURN CASE WHEN date (idate) IS NOT null THEN 1 ELSE 0 END;
END

Create that as a UDF and you've duplicated the SQL Server functionality for your own use.  If you like it, put it in a public schema.


Good Luck,
Kent

Thanks, that's appreciated BUT...
On this particular server my role is relegated to QA type duties, which precludes my ability to create, only to read.
The problem with attempting the conversion is that all it'll do is give an error when it fails, it won't point you to the failing value - much less allow you to write a query that specifically returns failing values.

I did find a solution that does accomplish this, using the following:

   -- DB2 doesn't have ISDATE(), so utilizing Common Table Expression to
   --1) prefilter date field by date format - return NULL if not a decodable string, then
   --2) use XMLQUERY to determine if properly formatted date actually contains a valid date.
   --3) return only dates that are either invalid format or invalid dates.
   --NOTE: replace ALL instances of fieldname - there are many, DB2 doesn't use alternative CASE syntax:
   WITH DATE_FORMAT(QA_DT, FORMAT_DT) as (
                       SELECT           fieldname,
                              CASE WHEN fieldname LIKE('____-__-__') THEN fieldname
                                   WHEN fieldname LIKE('__.__.____') THEN fieldname
                                   WHEN fieldname LIKE('__/__/____') THEN fieldname
                                   WHEN fieldname LIKE('20______')   THEN
                                       DATE(substr(fieldname,1,4)||'-'||substr(fieldname,5,2)||'-'||substr(fieldname,7,2))
                                   WHEN fieldname LIKE('19______')   THEN
                                       DATE(substr(fieldname,1,4)||'-'||substr(fieldname,5,2)||'-'||substr(fieldname,7,2))
                                   ELSE NULL END
                       FROM   tablename   )
   SELECT keyfield(s), QA_DT, DATE(FORMAT_DT) AS FDATE
   FROM DATE_FORMAT
   WHERE FORMAT_DT is NULL
      OR XMLCAST(XMLQUERY('string($D) castable as xs:date' PASSING FORMAT_DT AS D) AS VARCHAR(10)) = 'false'

Select allOpen in new window

I chose to build onto a solution I had already started using Common Table Expressions (the WITH component), so that it essentially first filters out date that isn't a "date format", then secondly validates that the stuff that looks like dates actually are dates, via XMLQUERY.

It should be easy to simplify this dramatically, to skip the WITH entirely, and skip right to the date validation using XMLQUERY. Either way, this functions as a row-wise pre-conversion date validation functionality like ISDATE(), returning rows that have invalid dates.