Coversion to date from Date time field

[aniruddhyadutta]

Hi All,

I want to convert the below date time field to date

it should give me as 10/21/2016 or 21-Oct-2016

I tried in my expression

date((left(DATETIME,8)),'MM/DD/YYYY')

dayname(DATETIME)

but they don't seem to work.

[ift_isabelle · Accepted solution]

Try:

Date(Makedate(mid(DATETIME,5,4), Left(DATETIME,2), mid(DATETIME,3,2)), 'MM/DD/YYYY')

[tresesco]

Try like:

Date(Date#(left(trim(DATETIME),8),'MMDDYYYY'))

Date#() - evaluates the expr. as a date according to the string given as format-code. I.e. it parses the string.

Date() - formats the date in given format. If the format code is omitted, the default date format set in the operating system is used.

[its_anandrjs]

Try this way also

Data:
LOAD * Inline
[
DateSource
1021201617,3418
];

Step1:
Load
*,
Date(Date#(Left(DateSource,8),'MMDDYYYY'),'MMDDYYYY') as NewDate
Resident Data;
DROP Table Data;

Step2:
LOAD *,
DATE(MakeDate(Year(NewDate),Month(NewDate),Day(NewDate)),'MM/DD/YYYY') as FinalDate1,
DATE(MakeDate(Year(NewDate),Month(NewDate),Day(NewDate)),'DD-MMM-YYYY') as FinalDate2

Resident Step1;
DROP Table Step1;

Regards,

Anand

[aniruddhyadutta]

Thanks Isabella for your suggestion!!

[aniruddhyadutta]

it worked!! thanks a ton