ISDATE equivalent of DB2

Question

I have a table which contains date in character format . I want to check the format of date . Please let me know how i can do this in DB2. I know there is a function ISDATE but its not working in DB2. I am on AS400 using db2 as date base .. Please help me out

Solution 1

Actually, it looks like DB2 for the AS/400 may not have the ISDATE() function (I can't find anything in the V6R1 reference - or, interestingly, the LUW reference either). So your problem appears to be that the function does not exist.

The root of the problem, of course, is that attempting to translate an invalid date causes the statement to halt. In light of that, this statement should give you a date if the formatting was possible, and null if it was not. Please note that if you've mixed USA and EUR formats/ordering, you might not be able to correctly recover the data (if your separators are different, which I think they are by default, you'll probably be okay).

WITH date_format(strDate, format) as (
    SELECT strDate, 
        CASE 
            WHEN strDate LIKE('____-__-__') THEN 'ISO'
            WHEN strDate LIKE('__.__.____') THEN 'EUR'
            WHEN strDate LIKE('__/__/____') THEN 'USA'
            ELSE NULL END
    FROM dataTable
)
SELECT
    strDate, 
    format, 
    CASE 
        WHEN format IS NOT NULL THEN DATE(strDate)
        ELSE NULL 
    END AS realDate
FROM date_format

This turns a dataTable looking like this:

String Dates
=============
2011-09-22   
22.09.2011   
09/22/2011   
a111x90x00  -- And who knows what this is...  

Into this:

Results:
strDate      format   realDate
============================
2011-09-22   ISO      2011-09-22   
22.09.2011   EUR      2011-09-22   
09/22/2011   USA      2011-09-22   
a111x90x00   -        -            

This example is of course using the default formats which auto-translate. If you have something else, you'll have to manually translate it (instead of returning the format, you can substring it into ISO then cast it).

Solution 2

I am not clear on what "check the format of date" intends, nor do I know what any existing ISDATE() effects, but what is implied by the name seems clear enough. Consider:

Extremely lightly tested on v5r3 [i.e. only to ensure both that a few bad date character string example values and some garbage text input actually returned NULL and that each of the various standard date formats with valid values returned a date value], that the following should effect the evaluation of the input varying character string up to 10 characters as a value that can be cast to DATE; that when the input value can not be cast to date, then the result is NULL. Of course that means an initially NULL value is not directly distinguishable from the result produced for an invalid date string.

If some desired indicator, such as TRUE or FALSE is desired as the result instead, then the use of the User Defined Function (UDF) could be coded in a CASE expression; e.g.:
CASE WHEN ISDATE(myVCcol) IS NULL THEN 'FALSE' ELSE 'TRUE' END

DROP   FUNCTION ISDATE                              
;                                                   
CREATE FUNCTION ISDATE                              
 ( InpDateStr VARCHAR( 10 )                         
 ) RETURNS DATE                                     
 LANGUAGE SQL                                       
 DETERMINISTIC                                      
 RETURNS NULL ON NULL INPUT                         
    SET OPTION DBGVIEW = *SOURCE , DATFMT = *ISO    
BEGIN                                               
 DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN NULL ;
 RETURN DATE( InpDateStr ) ;                        
END                                                 
;