Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try:
Date(Makedate(mid(DATETIME,5,4), Left(DATETIME,2), mid(DATETIME,3,2)), 'MM/DD/YYYY')
Try:
Date(Makedate(mid(DATETIME,5,4), Left(DATETIME,2), mid(DATETIME,3,2)), 'MM/DD/YYYY')
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.
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
Thanks Isabella for your suggestion!!
it worked!! thanks a ton