Date format from text

[#1]

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

[MayilVahanan]

HI

Try like this

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

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

Hope it helps

[CELAMBARASAN · Accepted solution]

Hi,

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

DateFormatting:

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

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

Or

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

From ExcelDatasource;

[whiteline]

Hi.

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).