Delimiter //
/**
 get date part
**/

Drop Procedure If Exists CASH_DATEPART//Create Procedure CASH_DATEPART (
	In value Varchar(255), In format Int, In datePartDelimiter Varchar(1),
	Out dVal Int, Out mVal Int, Out yVal Int, Out validFormat Int
)
Begin

	Declare part1End, part2End, century Int;
	Declare part1, part2, part3 Varchar(4);
	Declare formatMatch Varchar(50);

	Set dVal = 0;

	If format > 100 Then
		Set century = 100;
	Else
		Set century = 0;
	End If ;

	Set validFormat = format-century;

    cash_datepart_trt: Begin

        Case True

            When validFormat <= 0 Then
                Set validFormat = -10;
                Leave cash_datepart_trt;

            When validFormat > 1 And validFormat < 5 Then
                -- with deilimiter
                -- looking for datePartDelimiter
                -- set part1End = locate('/', format);
                If datePartDelimiter Is Null Then
                    Set validFormat = -11;
                    Leave cash_datepart_trt;
                Elseif datePartDelimiter = '' Then
                    Set validFormat = -12;
                    Leave cash_datepart_trt;
                End If;

                If validFormat = 4 Then
                    -- years first, day last
                    Set formatMatch = Concat('^([0-9]{2}){1,2}', datePartDelimiter, '[0-9]{1,2}', datePartDelimiter, '[0-9]{1,2}$');
                Else
                    -- years last
                    Set formatMatch = Concat('^[0-9]{1,2}', datePartDelimiter, '[0-9]{1,2}', datePartDelimiter, '([0-9]{2}){1,2}$');
                End If ;
                If not value regexp formatMatch Then
                    Set validFormat = -5;
                    Leave cash_datepart_trt;
                End If;

                Set part1End = locate(datePartDelimiter, value);
                Set part1 = substring(value, 1, part1End -1 );

                Set part2End = locate(datePartDelimiter, value, part1End+1);
                Set part2 = substring(value, part1End +1, part2End-part1End -1);
                Set part3 = substring(value, part2End +1);

                If validFormat=2 Then
                    Set dVal = cast(part1 as signed);
                    Set mVal = cast(part2 as signed);
                    Set yVal = cast(part3 as signed);
                Elseif validFormat=3 Then
                    Set dVal = cast(part2 as signed);
                    Set mVal = cast(part1 as signed);
                    Set yVal = cast(part3 as signed);
                Elseif validFormat=4 Then
                    Set dVal = cast(part3 as signed);
                    Set mVal = cast(part2 as signed);
                    Set yVal = cast(part1 as signed);
                End If ;
                -- return concat(dval, '--', mval, '--', yval);

            Else
                -- without datePartDelimiter => empty datePartDelimiter
                If century = 0 Then
                    -- with century
                    Set formatMatch = '^[0-9]{6}$';
                Else
                    -- without century
                    Set formatMatch = '^[0-9]{8}$';
                End If;
                If not value regexp formatMatch Then
                    Set validFormat = -5;
                    Leave cash_datepart_trt;
                End If;

                If century = 0 Or validFormat = 5 Then
                    Set part1End = 2;
                    Set part2End = 4;
                Else
                    Set part1End = 4;
                    Set part2End = 6;
                End If;

                Set part1 = substring(value, 1, part1End);
                Set part2 = substring(value, part1End + 1, part2End-part1End );
                Set part3 = substring(value, part2End + 1);

                If validFormat=1 Then
                    Set dVal = cast(part3 as signed);
                    Set mVal = cast(part2 as signed);
                    Set yVal = cast(part1 as signed);
                ElseIf validFormat=5 Then
                    Set dVal = cast(part1 as signed);
                    Set mVal = cast(part2 as signed);
                    Set yVal = cast(part3 as signed);
                End If;
                -- return concat(dval, '--', mval, '--', yval);

        End Case;

    End cash_datepart_trt;

End //
Delimiter ;
