DB2: How to validate date in String format which i

问题:

I need to know wheather a data in a column of type varchar is in correct date format or not . I have to do the same in DB2.

I have done this in java by using SimpleDateFormat() and Date.Parse() functions with the help of Exception handling .

I'm posting my java code to validate a string to date

private boolean isValidDate(String date) {
    try{
        DateFormat d = new SimpleDateFormat("yyyyMMdd");
        d.setLenient(false);
        d.parse(date);
        return true;
    }
    catch(Exception e){
        return false;
    }
}

but now i have to do the same in DB2 data base.Can i use any functions or procederes in DB2

my data in table column is like this..

20140231

20000101

.

.

.

yyyyMMdd

and column type is varchar

回答1:

My style of display the date:

 try
     {
 Date dob = new SimpleDateFormat("yyyy-MMM-dd").parse(request.getParameter("date"));
     user.setDate(date);
     }
       catch(ParseException e)
       {
           e.printStackTrace();
       }

note: if you should give like as yyyy-MMM-dd or MM/dd/yyyy

your programs as:

private boolean isValidDate(String date) 
{
try
{
DateFormat d = new SimpleDateFormat("MMM/dd/yyyy").parse(request.getParameter("date"));
        d.setLenient(false);
        d.parse(date);
        return true;
    }
    catch(Exception e)
    {
        return false;
    }
}

Timestamp to varchar:

timestamp-expression

An expression that returns a value that must be a DATE or TIMESTAMP, or a valid string representation of a date or timestamp that is not a CLOB or DBCLOB.

If the argument is a string, the format-string argument must also be specified.

In a Unicode database, if a supplied argument is a graphic string representation of a data, time, or timestamp, it is first converted to a character string before evaluating the function.

If timestamp-expression is a DATE or a valid string representation of a date, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00).

For the valid formats of string representations of datetime values, see "String representations of datetime values" in "Datetime values".

format-string

The expression must return a value that is a built-in CHAR, VARCHAR, numeric, or datetime data type.

If the value is not a CHAR or VARCHAR data type, it is implicitly cast to VARCHAR before evaluating the function.

In a Unicode database, if the supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function. The actual length must not be greater than 254 bytes (SQLSTATE 22007).

The value is a template for how timestamp-expression is to be formatted.

A valid format-string must contain a combination of the format elements listed below (SQLSTATE 22007).

Two format elements can optionally be separated by one or more of the following separator characters:

minus sign (-)
period (.)
slash (/)
comma (,)
apostrophe (')
semi-colon (;)
colon (:)
blank ( )

note:

click 1

click2

UPDATE:

sample code 1: Convert the current date to YYYYMM format

SELECT DATE_FORMAT(NOW(), '%Y%m');

o/p: # 201403

sample code 2: Convert the current date to YYYYMM format

 SELECT VARCHAR_FORMAT(CURRENT_DATE, 'YYYYMM') FROM jmail;

o/p # 201403

sample:

No | MySQL                         |      DB2                           |SampleOutput

1  | DATE_FORMAT(NOW(), '%Y-%m-%d) | VARCHAR_FORMAT(CURRENT_DATE,'YYYY-MM-DD')| 2013-02-14

2  | DATE_FORMAT(NOW(), '%d/%m/%y')| VARCHAR_FORMAT(CURRENT_DATE,'DD/MM/RR')  |  14/02/13

回答2:

It's not clear what "flavor" of DB2 is needed. With DB2 for i, I'd probably create a function to do the test and return an indication of success or failure. Here's an example that works for me:

DROP SPECIFIC FUNCTION SQLEXAMPLE.CHKVCDATE ; 

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SQLEXAMPLE" ; 

CREATE FUNCTION SQLEXAMPLE.CHKVCDATE ( 
    VCDATE VARCHAR(20) ) 
    RETURNS INTEGER   
    LANGUAGE SQL 
    SPECIFIC SQLEXAMPLE.CHKVCDATE 
    DETERMINISTIC 
    CONTAINS SQL 
    RETURNS NULL ON NULL INPUT 
    NO EXTERNAL ACTION 
    NOT FENCED 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *CHG , 
    CLOSQLCSR = *ENDMOD , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   

    BEGIN ATOMIC 

DECLARE CHKDATE DATE ; 
DECLARE VALIDDATE INT ; 

DECLARE NOT_VALID CONDITION FOR '22007' ; 
DECLARE CONTINUE HANDLER FOR NOT_VALID 
    SET VALIDDATE = -1 ; 

SET VALIDDATE = 0 ; 

VALUES ( VCDATE ) INTO CHKDATE ; 

RETURN VALIDDATE ; 

END  ; 

COMMENT ON SPECIFIC FUNCTION SQLEXAMPLE.CHKVCDATE 
    IS 'Check VARCHAR for valid date' ;

Any properties or other details that don't fit your particular DB2 can be removed or changed. The size of the VCDATE VARCHAR parm might need adjustment, and the RETURN value can be whatever you need. The function might be useful in a WHERE clause.

I can invoke it like this:

select sqlexample.chkvcdate('2014-02-29'), sqlexample.chkvcdate('2014-02-28') from sysibm.sysdummy1

The first will return ( -1 ) for the invalid value, and the second will return ( 0 ) for valid.