topic Re: Date is in text format. How to convert it? in QlikView

Date is in text format. How to convert it?

Anonymous — Fri, 20 Feb 2015 09:09:08 GMT

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?

Re: Date is in text format. How to convert it?

tresB — Fri, 20 Feb 2015 09:16:57 GMT

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

Re: Date is in text format. How to convert it?

Anonymous — Fri, 20 Feb 2015 09:20:02 GMT

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

Re: Date is in text format. How to convert it?

tresB — Fri, 20 Feb 2015 09:39:14 GMT

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

Re: Date is in text format. How to convert it?

Fri, 20 Feb 2015 09:45:34 GMT

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

Re: Date is in text format. How to convert it?

Fri, 20 Feb 2015 09:49:10 GMT

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

Re: Date is in text format. How to convert it?

Fri, 20 Feb 2015 09:50:18 GMT

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

Re: Date is in text format. How to convert it?

anbu1984 — Fri, 20 Feb 2015 09:50:28 GMT

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

Re: Date is in text format. How to convert it?

tresB — Fri, 20 Feb 2015 09:54:38 GMT

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

Re: Date is in text format. How to convert it?

Fri, 20 Feb 2015 10:07:34 GMT

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.

Re: Date is in text format. How to convert it?

mohammadkhatimi — Fri, 20 Feb 2015 10:21:27 GMT

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

Re: Date is in text format. How to convert it?

mohammadkhatimi — Fri, 20 Feb 2015 10:22:07 GMT

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

Re: Date is in text format. How to convert it?

Anonymous — Fri, 20 Feb 2015 11:03:28 GMT

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

Re: Date is in text format. How to convert it?

anbu1984 — Fri, 20 Feb 2015 11:46:16 GMT

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'