Date formatting

[adarsh_a]

Hi All,

In the excel file attached there are two date columns. Each column is having two date formats(spaces bfr date and without spaces).

I'm trying to convert into single format using Date# function but not able to achieve it in script(getting some values in date and some in number). 

Finally i need to extract Month,date and Year from this data.

Any help in resolving this is greatly appreciated. I have attached the excel file.

Regards,

Adarsh

[johnw]

Maybe something like this.

LOAD *
,date(monthstart([Create Date]),'MMM YYYY') as [Create Month]
,date(yearstart([Create Date]),'YYYY') as [Create Year]
;
LOAD
...
date(date#(subfield(trim([Create Date]),' ',1),'D/M/YYYY'),'D/M/YYYY') as [Create Date]
...
FROM somewhere
;

[pathiqvd]

Hi,

       If there different date formats in single column you can solve with alt function.Inside alt function just type whatever formats do you have and use date function you will get date format.

Table:

LOAD *,

  date(alt(date#(DT,'DD/MM/YYYY'),date#(DT,'DD-MM-YYYY'),date#(DT,'DD-MMM-YYYY')),'DD-MM-YYYY') as DT2 INLINE [

    DT

    13/10/2016

    14-10-2016

    15-Oct-2016

];

Regards,