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?

[mohammadkhatimi]

Hie Vijay,

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 this will help you,

Regards,

Mohammad

[mohammadkhatimi]

Hie Vijay,

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 this will help you,

Regards,

Mohammad

[Anonymous]

Thank you all for your efforts. Finally worked with

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

Still breaking my head to understand this solution. But I have got the result

[anbu1984]

From QV manual,

TextBetween(s , beforetext , aftertext [, n ])

Returns the text between the n:th occurrence of beforetext and the immediately following occurrence of aftertext within the string s.

Examples:

TextBetween('<abc>', '<', '>') returns 'abc

>TextBetween('<abc><de>', '<', '>',2) returns 'de'

TextBetween() returns string between 'day,' and '#'. Added # to date field which will be aftertext

TextBetween('Thursday, July 31, 2014 04:04:54 PM' & '#', 'day,','#') -- Returns 'July 31, 2014 04:04:54 PM'