25 Replies Latest reply: May 10, 2013 1:56 AM by Prashant Adhav RSS

    Date Conversion strange behaviour

    Lav Jain

      Hi,

       

      I'm using the script below to convert the dates in the attached excel sheet.

       

      LOAD Name,

       

      date(alt(date#([date] , 'YYYY/MM/DD' ),date#([date], 'MM/DD/YYYY' ),date#( [date] , 'MM/DD/YY' ),date#([date], 'DD/MM/YYYY' ),

           date#([date], 'MM/DD/YYYY'),date#([date], 'M-DD-YY' ),Date#([date], 'DD/MM/YY'),

           date#([date], 'MM-DD-YY' ), num#([date], 'M-DD-YY' ),0))  as date

      FROM

      test.xlsx

      (ooxml, embedded labels, table is Sheet1);

       

       

      Please note that although the format 'YYYY/MM/DD'  is not present in the excel sheet but still i'm using it as this format used to be there in an earleir version of the file & could be there in future also.

       

      The problem is the conversion is correct for every name exect for name: I for which I get :

      13/02/2028
      13/04/2030
      12/06/2030
      13/01/2031
      13/03/2031

       

      My set/default date format in the file is DD/MM/YYYY.

       

      When i remove date#([date] , 'YYYY/MM/DD' ) from the alt then it works fine.

       

      Why is this happening ??

       

      Regards