Date is in text format. How to convert it?

[Anonymous]

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?

[tresesco]

Date(Date#('July 31, 2014 04:04:54 PM', 'MMMM DD, YYYY hh:mm:ss TT'))

[Anonymous]

But what should I do with Thursday? Since I am having Thursday, before July 31, 2014 ................. it is not getting converted.

[tresesco]

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

[#5]

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

[#6]

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

[#7]

Date# doesn't like the time elements in it I believe

[anbu1984 · Accepted solution]

=Date( Date#(Trim(TextBetween('Thursday, July 31, 2014 04:04:54 PM' & '#', 'day,','#')) , 'MMMM DD, YYYY hh:mm:ss TT'))

[tresesco]

I don't know if it LIKEs or not but it TAKEs , and parse successfully.

[#10]

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.