Topic
5 replies Latest Post - ‏2013-02-20T17:49:48Z by tells
tells
tells
12 Posts
ACCEPTED ANSWER

Pinned topic What is the purpose of the is_date check?

‏2012-12-20T21:58:58Z |
I was trying to build a rule where I use is_date function, but I am not getting expected results. It seems to only fail if I put letters into the field.

Logic:
cost_dt is_date

Test Data:
20121010
201214
20121445
201X01

The only record that fails is 201X01.

I was expecting 14 to fail as an invalid month or 45 to fail as an invalid day.

So then I was thinking, how does is_date know which fields are years, days, months.

How is the is_date check usually used and does it check for valid months, days, and the combination of months and days?
Updated on 2013-02-20T17:49:48Z at 2013-02-20T17:49:48Z by tells
  • smithha
    smithha
    162 Posts
    ACCEPTED ANSWER

    Re: What is the purpose of the is_date check?

    ‏2012-12-21T12:55:08Z  in response to tells
    The IS_DATE function checks whether the source data, which must be a character data type, is in a valid date format.

    As noted in the documentation, it evaluates the character string to see whether it satisfies any of a number of different formats. See: http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r7/topic/com.ibm.swg.im.iis.ia.quality.doc/topics/dq_date_check.html

    If you look at your test data vs. the chart shown in the doc:

    20121010 - satisfied by this format: %yyyy%mm%dd
    201214 - satisfied by this format: %yy%mm%dd
    20121445 - satisfied by this format: %dd%mm%yyyy
    201X01 - as you noted, contains character, so fails

    The IS_DATE function does not know which fields are years, days, or months, but simply checks to see whether one of the formats is applicable (i.e. it ensures that a character string conforms to a date structure).

    To validate the month or day you need to know the correct format for your data and test the relevant substring (e.g. month) to be in the correct range.

    Harald
    • tells
      tells
      12 Posts
      ACCEPTED ANSWER

      Re: What is the purpose of the is_date check?

      ‏2012-12-21T14:13:00Z  in response to smithha
      Here is my current logic:

      cost_dt is_date
      and substring(cost_dt,5,2) matches_regex '^((01-9)|(10-2))$'
      and substring(cost_dt,7,2) matches_regex '^(((0)1-9)|((1|2)0-9)|(30-1))$'
      and substring(cost_dt,1,4) in_reference_list {'2012','2013'}

      This tests for correct number ranges for format YYYYMMDD.

      Is there any easy method for making sure the combination of values are correct without writing multiple rules?

      I was thinking I would need multiple rules to check for the correct combinations.

      For example:
      1 rule to verify that if month matches 01,03,05,07,08,10,12 then the days range should be 01 - 31.
      1 rule to verify that if month matches 04,06,09,11 then the days range should be 01 - 30.
      1 rule to verify that if month matches 02 then the days should usually be 28.

      I don't see any way to create a rule that has 3 if statements, so I was thinking I would need at least 3 rules to do this type of check. Or is there an easier way with some built in function?
      • smithha
        smithha
        162 Posts
        ACCEPTED ANSWER

        Re: What is the purpose of the is_date check?

        ‏2012-12-21T17:49:15Z  in response to tells
        Assuming a consistent format for the date, you could consolidate the regex/ref list logic down to one regex line avoiding the substring altogether. You can find various examples for that just doing a search on 'regex to validate a date' or similar.

        As for the validation for month and day, there is no case function or nested if logic currently so you would need to create 3 rules, though you can group those together into 1 rule set which acts like a case function and only needs one passthru of the data (and would give you specifics as to which records failed each specific condition).

        Harald
  • tells
    tells
    12 Posts
    ACCEPTED ANSWER

    Re: What is the purpose of the is_date check?

    ‏2013-01-03T19:02:58Z  in response to tells
    I found a better way for checking dates that seems to work and find invalid dates like 20120230.

    Logic: datevalue(cost_dt,'%yyyy%mm%dd') exists

    In my testing, if you pass values that are not valid dates to the datevalue function it returns a null. So if the date is value it will pass the exists check.
  • tells
    tells
    12 Posts
    ACCEPTED ANSWER

    Re: What is the purpose of the is_date check?

    ‏2013-02-20T17:49:48Z  in response to tells
    I think this issue was fixed in this patch.

    http://www-01.ibm.com/support/docview.wss?rs=14&uid=swg1JR39542