Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My date column name is "MyDate" and the data is in text format as
Thursday, July 31, 2014 04:04:54 PM |
QlikView 11 is not identifying this as date. How to extract date and time from this kind of data?
=Date( Date#(Trim(TextBetween('Thursday, July 31, 2014 04:04:54 PM' & '#', 'day,','#')) , 'MMMM DD, YYYY hh:mm:ss TT'))
Date(Date#('July 31, 2014 04:04:54 PM', 'MMMM DD, YYYY hh:mm:ss TT'))
But what should I do with Thursday? Since I am having Thursday, before July 31, 2014 ................. it is not getting converted.
Ideally,
Date(Date#('Thursday, July 31, 2014 04:04:54 PM', 'WWWW, MMMM DD, YYYY hh:mm:ss TT'))
should work, but not working(I tested) and trying to find the issue. By the time you can try an alternative like:
Date( Date#(Trim(SubField(YourDateField, ',',2)) , 'MMMM DD, YYYY hh:mm:ss TT'))
Hi Vijay,
Please try following script
Load *,
Date(Date#(Right(FD,Len(FD)-Len(SubField(FD,','))-2), 'MMMM DD, YYYY hh:mm:ss TT'),'dd-MM-yyyy hh:mm:ss TT') As ConvertedDate;
Load * Inline
[FD
'Thursday, July 31, 2014 04:04:54 PM'
];
Regards,
Faisal M A
Hi Vijay,
use the below
Date(Date#(Subfield('Thursday, July 31, 2014 04:04:54 PM',',',2)&Left(Subfield('Thursday, July 31, 2014 04:04:54 PM',',',3),5),' MMMM DD YYYY'))
hope that helps
Joe
Date# doesn't like the time elements in it I believe
=Date( Date#(Trim(TextBetween('Thursday, July 31, 2014 04:04:54 PM' & '#', 'day,','#')) , 'MMMM DD, YYYY hh:mm:ss TT'))
I don't know if it LIKEs or not but it TAKEs , and parse successfully.
I stand corrected, I thought it was the time causing the issue but it's adding the WWWW in there seems to cause an issue.