PIG date conversion issue sample date is 4/1/2009 9:06:00 AM

Question

bill_details= FOREACH bill_details_raw GENERATE 
              $0 AS invD_icode, $1 AS invD_InvH_icode, 
              $2 AS invD_InvH_No,
              ToDate(invD_InvH_date,'MM/dd/yyyy hh:mm:ss aa') AS dateString,  
              $5 AS invD_pro_icode,
              $6 AS invD_pro_ucode, 
              $7 AS invD_pro_name,  
              $8 AS invD_qty;



java.lang.Exception: org.apache.pig.backend.executionengine.ExecException: ERROR 0: Exception while executing [POUserFunc (Name: POUserFunc(org.apache.pig.builtin.ToDate2ARGS)[datetime] - scope-85 Operator Key: scope-85) children: null at []]: java.lang.IllegalArgumentException: Invalid format: " invD_InvH_date"
    at org.apache.hadoop.mapred.LocalJobRunner$Job.runTasks(LocalJobRunner.java:489)
    at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:549)
Caused by: org.apache.pig.backend.executionengine.ExecException: ERROR 0: Exception while executing [POUserFunc (Name: POUserFunc(org.apache.pig.builtin.ToDate2ARGS)[datetime] - scope-85 Operator Key: scope-85) children: null at []]: java.lang.IllegalArgumentException: Invalid format: " invD_InvH_date"
    at org.apache.pig.backend.hadoop.executionengine.physicalLayer.PhysicalOperator.getNext(PhysicalOperator.java:338)
    at org.apache.pig.backend.hadoop.executionengine.physicalLayer.relationalOperators.POForEach.processPlan(POForEach.java:378)
    at org.apache.pig.backend.hadoop.executionengine.physicalLayer.relationalOperators.POForEach.getNextTuple(POForEach.java:298)
    at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigGenericMapBase.runPipeline(PigGenericMapBase.java:282)
    at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigGenericMapBase.map(PigGenericMapBase.java:277)
    at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigGenericMapBase.map(PigGenericMapBase.java:64)
    at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
    at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:793)
    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
    at org.apache.hadoop.mapred.LocalJobRunner$Job$MapTaskRunnable.run(LocalJobRunner.java:270)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalArgumentException: Invalid format: " invD_InvH_date"
    at org.joda.time.format.DateTimeFormatter.parseDateTime(DateTimeFormatter.java:899)
    at org.apache.pig.builtin.ToDate2ARGS.exec(ToDate2ARGS.java:45)
    at org.apache.pig.builtin.ToDate2ARGS.exec(ToDate2ARGS.java:33)
    at org.apache.pig.backend.hadoop.executionengine.physicalLayer.expressionOperators.POUserFunc.getNext(POUserFunc.java:330)
    at org.apache.pig.backend.hadoop.executionengine.physicalLayer.expressionOperators.POUserFunc.getNextDateTime(POUserFunc.java:422)
    at org.apache.pig.backend.hadoop.executionengine.physicalLayer.PhysicalOperator.getNext(PhysicalOperator.java:329)
    ... 14 more

Answer 1

bill_details= FOREACH bill_details_raw GENERATE $0 AS invD_icode,
             $1 AS invD_InvH_icode, 
             $2 AS invD_InvH_No, 
            ToDate($3,' M/dd/yyyy hh:mm:ss aa') AS dateString,
            $5 AS invD_pro_icode, 
            $6 AS invD_pro_ucode, 
            $7 AS invD_pro_name, 
            $8 AS invD_qty;