Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: Search for Invalid date

    Hi there,

    I have a table which contains 7 million records, and I want to find the ones with invalid date.

    The date field is char(8). Instead of doing something like

    substr(CLAIM_INJURY_DATE, 1, 4) between '0001' and '9999'
    and substr(CLAIM_DATE, 5, 2) between '01' and '12'
    and substr(CLAIM_DATE, 7, 2) between '01' and '31'
    ... (I have not checked Feb 29, Sep 31.. etc yet)

    Is there any easy and quick way to select the invaild date?

    Thanks,
    Jinse

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    date

    how about date(colname) this will check if character string can be migrated to date. use also substr and concat to get the correct input format
    substr(colname,1,4)||'-'|| ....... --> 2006-12-31
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Aug 2006
    Posts
    33
    Quote Originally Posted by guyprzytula
    how about date(colname) this will check if character string can be migrated to date. use also substr and concat to get the correct input format
    substr(colname,1,4)||'-'|| ....... --> 2006-12-31

    If the date is invalid, am I able to catch the error in date(colname)? So I can write something like:

    select case when date(colname) = ? then 'Invalid'
    when date(colname) = ?? then 'Valild'
    end as Indicator

Posting Permissions

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