Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
kvijay173
Creator
Creator

Date is in text format. How to convert it?

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?

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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

View solution in original post

13 Replies
tresesco
MVP
MVP

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

kvijay173
Creator
Creator
Author

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

tresesco
MVP
MVP

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

Not applicable

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

Not applicable

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

Not applicable

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

anbu1984
Master III
Master III

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

tresesco
MVP
MVP

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

Not applicable

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.