Create Table + Join + Max 'String' Date

[Ambastha, Abhishek]

Hi,

I want to write a pig query for the below SQL:

                CREATE TABLE MAXDATE AS
                SELECT SERIAL_NUM, MAX(REPORTING_DT) AS REPORTING_DT
                FROM Table_1
                WHERE REPORTING_DT <= '01APR2013' AND
                SERIAL_NUM IN (SELECT SERIAL_NUM FROM TABLE_2) AND
                SERIAL_NUM NOT IN (SELECT SERIAL_NUM FROM TABLE_3)
                GROUP BY SERIAL_NUM;

REPORTING_DT is a string but I want Maximum Date. Probably, I could use 
CustomFormatToISO for Date conversion and get the Maximum Value.

Join is on Table_1, Table_2 and Table_3.

Please suggest how to do this.

Regards,
Abhishek