Geek Answers Handbook

ISDATE equivalent to DB2

I have a table that contains a date in character format. I want to check the date format. Please let me know how I can do this in DB2. I know that there is an ISDATE function, but it does not work in DB2. I am on an AS400 using db2 as the date base. Please help me

+2
source share
2 answers

In fact, it looks like DB2 for AS / 400 might not have a function ISDATE()(I cannot find anything in the V6R1 link - or, interestingly, the LUW link). So your problem is that the function does not exist.

The root of the problem, of course, is that trying to translate an invalid date causes the operator to stop. In light of this, this statement should give you a date if formatting is possible, and null if it is not. Please note that if you mixed US / EUR formats / orders, you may not recover the data correctly (if your separators are different and I think they are the default, you will probably be all right).

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 causes the dataTable to look like this:

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

In it:

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, of course, uses the default formats, which are automatically translated. If you have something else, you will have to manually translate it (instead of returning the format, you can fine-tune it to ISO and then quit).

+3

, " ", , ISDATE(), , , . :

v5r3 [.. , , NULL ], 10 , DATE; , , NULL. , , NULL , .

, TRUE FALSE, , (UDF), CASE; : 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                                                 
;
+1

More articles: