Results 1 to 5 of 5

Thread: Date Validation

  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Unanswered: Date Validation

    Is it possible to validate all the rows in a table for a date column and get those invalid rows out using a single sql or using a date function in UDB V7

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Re: Date Validation

    What do you mean with "invalid"? If you defined your column as "date" you'll have only "valid" dates there. If you call "invalid" the dates that are out of some range, you can delete them with a
    DELETE FROM <tabname> WHERE <date_column> BETWEEN <begin_range> AND <end_range>.
    If you're talking about a column that allows NULL, you can also use a
    WHERE <date_column> IS NULL

    HTH
    Rodney Krick

  3. #3
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Re: Date Validation

    As you told me per mail, you have a char column where some values are invalid. There are many ways to "clean" this situation. If you want to try with UDF, I give you here an example:
    Write a UDF to check the values

    (example in Java)
    <java>
    import java.sql.Date;
    public class DateFunctions {
    public static int isDate(String s) {
    try {
    Date d = Date.valueOf(s);
    } catch (Exception e) {
    return 0;
    }
    return 1;
    }
    }
    </java>

    Compile the DateFunctions.java file (javac ...)
    Put the .class file under %DB2PATH%\function

    Create the UDF:
    <udf>
    create function isDate(char(10)) returns integer fenced language java parameter style java external name 'DateFunctions!isDate' no external action called on null input deterministic no sql
    </udf>

    Now you can "clean" your data:

    DELETE FROM <table> WHERE isDate(<char_column>) = 0

    HTH
    Rodney Krick

  4. #4
    Join Date
    Oct 2002
    Posts
    4

    Date Validation by UDF

    Is it possible to do a UDF for date validation in SQL. I have to do a validation on CHAR COLUMN OF LENGTH 8 and I want to identify all the rows which violates DATE validation. I want to execute as normal functions on the column and I do not want it to be row by row processing

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I haven't really used user defined functions in db2. From what I understand, they are pretty limited if you attempt to write them in SQL. I'd be interested to see any examples you might have though.

    Assuming that your 8 byte char column is of the format ddmmyyyy, you could use the following. I haven't taken leap years into account and I've limited it to years 1900 - 2100, so you might want to tweak it.

    SELECT <DATE_COLUMN>
    , CASE WHEN SUBSTR(<DATE_COLUMN>,1,2) BETWEEN '01' AND '31'
    THEN
    'TRUE'
    ELSE
    'FALSE'
    END
    FROM <TARGET_TABLE>
    WHERE SUBSTR(<DATE_COLUMN>,3,2) IN ('01','03','05','07','08','10','12')
    AND SUBSTR(<DATE_COLUMN>,5,4) BETWEEN '1900' AND '2100'
    UNION
    SELECT <DATE_COLUMN>
    , CASE WHEN SUBSTR(<DATE_COLUMN>,1,2) BETWEEN '01' AND '30'
    THEN
    'TRUE'
    ELSE
    'FALSE'
    END
    FROM <TARGET_TABLE>
    WHERE SUBSTR(<DATE_COLUMN>,3,2) IN ('04','06','09','11')
    AND SUBSTR(<DATE_COLUMN>,5,4) BETWEEN '1900' AND '2100'
    UNION
    SELECT <DATE_COLUMN>
    , CASE WHEN SUBSTR(<DATE_COLUMN>,1,2) BETWEEN '01' AND '28'
    THEN
    'TRUE'
    ELSE
    'FALSE'
    END
    FROM <TARGET_TABLE>
    WHERE SUBSTR(<DATE_COLUMN>,3,2) IN ('02')
    AND SUBSTR(<DATE_COLUMN>,5,4) BETWEEN '1900' AND '2100'
    UNION
    SELECT <DATE_COLUMN>
    , 'FALSE'
    FROM <TARGET_TABLE>
    WHERE SUBSTR(<DATE_COLUMN>,3,2) NOT BETWEEN '01' AND '12'
    OR SUBSTR(<DATE_COLUMN>,5,4) NOT BETWEEN '1900' AND '2100'
    ;

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •