Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: date validation function

    Hi

    What is the function to validate date in the select query?

    Thanks,
    Waseem

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Note: I have selected yyyymmdd format.....your SQL will depend on the type of date format which you use......

    -checking yyyy lies between '0001' and '9999'
    SUBSTR(yyyymmdd,1,4) BETWEEN '0001' AND '9999'
    as a constraint you can check values within acceptable year range e.g between 1900 and 2100

    -checking mm lies between '01' and '12'
    SUBSTR(yyyymmdd,5,2) BETWEEN '01' AND '12'
    -and the trickiest part, checking the days dd which can lie between 0 - 28,29,30,31
    INT(SUBSTR(yyyymmdd,7,2)) BETWEEN 1 AND INT(DAY(DATE(CHAR(INSERT(SUBSTR(yyyymmdd,1,6),5,0, '-')||'-01',10))+ 1 MONTH - 1 DAY))

    So your final code can be -

    select
    case
    when SUBSTR(yyyymmdd,1,4) BETWEEN '1900' AND '2100' --Making sure that year lies within acceptable range
    then
    case
    WHEN SUBSTR(yyyymmdd,5,2) BETWEEN '01' AND '12'
    THEN
    CASE
    WHEN (INT(SUBSTR(yyyymmdd,7,2)) BETWEEN 1 AND INT(DAY(DATE(CHAR(INSERT(SUBSTR(yyyymmdd,1,6),5,0, '-')||'-01',10))+ 1 MONTH - 1 DAY)))
    then 1
    else 0
    end
    ELSE 0
    END
    ELSE 0
    END CASE
    FROM Tablename
    where
    1 = sucess
    0 = failure

    and yyyymmdd denotes the column name which needs to be validated
    Last edited by nick.ncs; 12-29-08 at 12:12.
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Jan 2005
    Posts
    80
    thanks for the code. However, I was looking for some functions that could do that?

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    well don't be lazy now.... look around as how to write UDF's and apply the above logic.....
    IBM Certified Database Associate, DB2 9 for LUW

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    It all depends on what you mean by verify. What Nick gave you is a perfectly good example that would work well within a UDF. Then there is a more direct route as well:
    SELECT DATE(:MY-DATE) FROM SYSIBM.SYSDUMMY1;
    You can do this in different date formats as well, but its a quick easy way to find out if the date is good, You can do it cheaper using a set host variable as well.

    Dave

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Adding on top of what Dave said, you can do this SELECT statement inside a procedure. Extend the procedure with an exception handler that catches invalid formats and does whatever you want to do instead. Now you write a UDF that calls the procedure. For an example, search for SOFT_INT, which is a function written by Serge Rielau and it does the same - just for integers.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nick.ncs
    Note: I have selected yyyymmdd format.....your SQL will depend on the type of date format which you use......

    -checking yyyy lies between '0001' and '9999'
    I guess you know that this will not work reliably because:
    Code:
    $ db2 "VALUES CASE WHEN '1ABC' BETWEEN '0001' AND '9999' THEN 'match' ELSE 'no match' END"
    
    1
    --------
    match
    
      1 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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