3 Replies Latest reply: Nov 2, 2012 4:48 AM by whiteline _ RSS

    Date format from text

      I loaded a excel file which has a date field but when I tried to pull Day(FIELD),Month(FIELD) I am getting nothing, That field contains data for One month but still I am getting nothing for Day(Field)....please help me

        • Re: Date format from text
          Mayil Vahanan Ramasamy



          Try like this


          Load *,day(Date) as Day, Month(Date) as Month;

          Load *,Date#(DateField) as Date from excelfilename.xls;


          Hope it helps

          • Re: Date format from text
            Celambarasan Adhimulam


            You have to format to date if it is not in correct format

            Assuming my date field is in the format MM-DD-YYYY then

            Check with this



            Load Day(FieldName) AS day,Month(FieldName) AS Month;

            Load Date#(DateField,'MM-DD-YYYY') AS FieldName From ExcelDatasource;



            Load Day(Date#(DateField,'MM-DD-YYYY')) AS day,Month(Date#(DateField,'MM-DD-YYYY')) AS Month

            From ExcelDatasource;

            • Re: Date format from text
              whiteline _



              To be able to use Date functions you should store your date in field in a right format.

              Actually dates has two parts: text and numeric. It seems that you have just string in FIELD so date functions doesn't work.


              So you should convert it to make a date from string. There are functions with # in the end that converts string into dual.

              Above you can see the example of conversion in script.

              You can also try it in a chart/listbox to make sure that the statement is correct (if you have loaded FIELD separately).