Extract and use date from memo field

[Cardstang]

thread767-1650510

I have a question similar to the one asked in the referenced thread...

I'm trying to extract a date from the memo field, but in this case, the date is not always the same length...meaning that August 8, 2012 is displayed as 8/8/2012, but October 8 is displayed as 10/8/2012, and October 10 is displayed as 10/10/2012. There are no leading zeros in front of Jan-Sept or days less than the 10th.

I'm able to extract a usable date for a MM/DD/YYYY format using the below formula, but a M/D/YYYY, MM/D/YYYY, or M/DD/YYYY does not work.

 date(mid(mid({memo.field},instr({memo.field},"Scheduled Start Date / Time (local time zone): ")+47),1,10))

As you should be able to tell from the formula, the date comes after the text "Scheduled Start Date / Time (local time zone): ".

Any way to extract the date if it's not always in the same MM/DD/YYYY format?

Thanks!

 

[hilfy]

I would do this in two parts. First, get the string that starts with your date. Then find the next space in that string and create a date from the beginning of that string to the space.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[fisheromacse]

if you use a portion of the formula you already have but without the date, do you get the string you need (or as likely, more than you need?)

using:
mid(mid({memo.field},instr({memo.field},"Scheduled Start Date / Time (local time zone): ")+47),1,10)
i am going to guess you get values similar to: "10/12/2012" and "1/3/2012 a" and "4/23/2012 "

If so, then i think you could use a formula to parse out the extra characters using Dell's suggestion above.

something like:

//{@finddate}
stringvar vard := mid(mid({memo.field},instr({memo.field},"Scheduled Start Date / Time (local time zone): ")+47),1,10);
IF instr(vard," ")=FALSE then date(vard)
else date(LEFT(vard,instr(vard," ")))

 

[CoSpringsGuy]

as long as "Scheduled Start Date / Time (local time zone): " only appears once in the memo field and the date is the next field
ex....
"memo stuff. lots of text. more text. etc.... etc.... Scheduled Start Date / Time (local time zone): 1/01/12 even more stuff... "

then this should work fine

date(split(split({memo.field},"Scheduled Start Date / Time (local time zone): ")[2]," ")[1])

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[Cardstang]

CoSpringsGuy, that worked fantastically.

I'm playing around with the formula to try to extract other tidbits. This site is great for helping me learn how things work.

Thanks again