Delimiter //
/**
 * Test of CASH_DATEPART()
 * @test
 */
Drop Procedure If Exists TEST_CASH_DATEPART//
Create Procedure TEST_CASH_DATEPART() 
Begin

    -- '1'=>'@^((?:[YyAa]{2}){1,2})([/-]{0})((?:[mM]{1,2}){1,2})\\2((?:[JDjd]{1,2}){1,2})$@', // YMD
    -- '2'=>'@^((?:[JDjd]{1,2}){1,2})([/-])((?:[mM]{1,2}){1,2})\\2((?:[YyAa]{2}){1,2})$@', // D/M/Y
    -- '3'=>'@^((?:[mM]{1,2}){1,2})([/-])((?:[JDjd]{1,2}){1,2})\\2((?:[YyAa]{2}){1,2})$@', // M/D/Y
    -- '4'=>'@^((?:[YyAa]{2}){1,2})([/-])((?:[mM]{1,2}){1,2})\\2((?:[JDjd]{1,2}){1,2})$@', // Y/M/D
    -- '5'=>'@^((?:[JDjd]{1,2}){1,2})([/-]{0})((?:[mM]{1,2}){1,2})\\2((?:[YyAa]{2}){1,2})$@', // DMY

    -- init
    Declare validFormat_1, dVal_1, mVal_1, yVal_1 Int;
    Declare validFormat_2, dVal_2, mVal_2, yVal_2 Int;
    Declare validFormat_3, dVal_3, mVal_3, yVal_3 Int;
    Declare validFormat_4, dVal_4, mVal_4, yVal_4 Int;
    Declare validFormat_5, dVal_5, mVal_5, yVal_5 Int;

    -- tested procedure
    Call CASH_DATEPART('970615', 1, Null, dval_1, mval_1, yVal_1, validFormat_1);
    Call CASH_DATEPART('16-07-1998', 2, '-', dval_2, mval_2, yVal_2, validFormat_2);
    Call CASH_DATEPART('05/14/96', 3, '/', dval_3, mval_3, yVal_3, validFormat_3);
    Call CASH_DATEPART('00-08-17', 4, '-', dval_4, mval_4, yVal_4, validFormat_4);
    Call CASH_DATEPART('13041995', 105, '-', dval_5, mval_5, yVal_5, validFormat_5);

    -- assertions
    -- format 1
    Call utAssert_eq('The day of an YMD formated date is correctly parsed'
        , 15
        , dval_1
    );
    Call utAssert_eq('The month of an YMD formated date is correctly parsed'
        , 06
        , mVal_1
    );
    Call utAssert_eq('The year of an YMD formated date is correctly parsed'
        , 97
        , yVal_1
    );
    Call utAssert_eq('The returned format stay unchanged for an YMD formated date'
        , 1
        , validFormat_1
    );
    
    -- format 2
    Call utAssert_eq('The day of an D/M/Y formated date is correctly parsed'
        , 16
        , dval_2
    );
    Call utAssert_eq('The month of an D/M/Y formated date is correctly parsed'
        , 07
        , mVal_2
    );
    Call utAssert_eq('The year of an D/M/Y formated date is correctly parsed'
        , 1998
        , yVal_2
    );
    Call utAssert_eq('The returned format stay unchanged for an D/M/Y formated date'
        , 2
        , validFormat_2
    );
    
    -- format 3
    Call utAssert_eq('The day of an M/D/Y formated date is correctly parsed'
        , 14
        , dval_3
    );
    Call utAssert_eq('The month of an M/D/Y formated date is correctly parsed'
        , 05
        , mVal_3
    );
    Call utAssert_eq('The year of an M/D/Y formated date is correctly parsed'
        , 96
        , yVal_3
    );
    Call utAssert_eq('The returned format stay unchanged for an M/D/Y formated date'
        , 3
        , validFormat_3
    );
    
    -- format 4
    Call utAssert_eq('The day of an Y/M/D formated date is correctly parsed'
        , 17
        , dval_4
    );
    Call utAssert_eq('The month of an Y/M/D formated date is correctly parsed'
        , 08
        , mVal_4
    );
    Call utAssert_eq('The year of an Y/M/D formated date is correctly parsed'
        , 00
        , yVal_4
    );
    Call utAssert_eq('The returned format stay unchanged for an Y/M/D formated date'
        , 4
        , validFormat_4
    );
    
    -- format 5
    Call utAssert_eq('The day of an DMY formated date is correctly parsed'
        , 13
        , dval_5
    );
    Call utAssert_eq('The month of an DMY formated date is correctly parsed'
        , 04
        , mVal_5
    );
    Call utAssert_eq('The year of an DMY formated date is correctly parsed'
        , 1995
        , yVal_5
    );
    Call utAssert_eq('The returned format stay unchanged for an DMY formated date'
        , 5
        , validFormat_5
    );

End//
Delimiter ;