Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Convert a bad string to a date

I have a field that I'm trying to extract a date from.

It's in the format, text February 2012 text.

Using the functions subfield and mid I now have February 2012.

How can I convert this to a date field, e.g. 01/02/2012

Regards

Tony

Message was edited by: QlikQuest Thanks for the response. I'm not at the office but I've created  a similiar scenario. Thanks again.

1 Solution

Accepted Solutions
jagannalla
Partner - Specialist III
Partner - Specialist III

If this in the case. Use this code,

Date(Date#(Trim(SubField(mid(baddate1,7),'/')),'MMM YYYY'),'DD/MM/YYYY') as DateField

View solution in original post

17 Replies
Not applicable
Author

Can u upload the data file by chance??

Not applicable
Author

try with

date(yourdate#('01/' & left(yourdate,3) & '/' & right(yourdate,4),'DD/MMM/YYYY')) as newdate

Not applicable
Author

whoops just noticed I made a mistake.

try with

load date(date#('01/' & left([Bad Date],3) & '/' & right([Bad Date],4),'DD/MMM/YYYY')) as newdate

Not applicable
Author

Juan,

You are a top guy.

I've amended your suggestion to include the mid function to strip out the bad text and it works a treat.

date(date#('01/' & left(mid(baddate,3),3) & '/' & right(mid(baddate,3),4),'DD/MMM/YYYY')) as newdate,

Many Many thanks.

Tony

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I don't think there is any need to substring the baddate. You should be able to read it directly with Date#() by supplying the mask.

date(Date#(baddate, 'MMM YYYY'), 'DD/MMM/YYYY')

-Rob

http://robwunderlich.com

adhudson
Creator II
Creator II

Hi,

     Yes. I agree with Rob.

     It is working perfectly without any use of string functions.

Regards

Andrew Hudson

Not applicable
Author

Rob, Andrew,

Thanks for your reply.

When I your suggestion,  date(Date#(baddate, 'MMM YYYY'), 'DD/MMM/YYYY')

The script executes OK but no data is returned.

If you could offer any suggestions I'd appreciate it.

Thanks

Tony

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Tony, can you provide a few sample lines?

-Rob

Not applicable
Author

Rob,

Thanks for the reply.

I've attached a simple spreadsheet with the type of string supplied from the user and

a QV doc applying your suggestion.

However no data is returned when I apply date(date# etc.

I really would like not to have to substring the field as per your suggestion.

Thanks

Tony