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