Retrieving a date value from a string

[Oscar115]

I have been asked to retrieve a date value from a string db field, field name is {HCOMMENT.CLOB}

A sample string value looks like the following:
LEASE APPT SCHEDULE BY PHONE / APPT IS FOR 2/17/2010 2:30PM.

I am attempting to get the date 2/17/2010 from this string.

I have defined the following using lookslike and am not getting any values back, note {wlcerti.dissuedate} is a datetime field.

if lookslike({HCOMMENT.CLOB},"?/?/??") then
date({wlcerti.dissuedate})
else
if lookslike({HCOMMENT.CLOB},"?/?/????") then
date({wlcerti.dissuedate})
else
if lookslike({HCOMMENT.CLOB},"?/??/??") then
date({wlcerti.dissuedate})
else
if lookslike({HCOMMENT.CLOB},"?/??/????") then
date({wlcerti.dissuedate})
else
if lookslike({HCOMMENT.CLOB},"??/??/??") then
date({wlcerti.dissuedate})
else
if lookslike({HCOMMENT.CLOB},"??/??/????") then
date({wlcerti.dissuedate})

I am certain that the date I am trying to pull from the string is a value in the db field wlcerti.dissuedate

Thanks!

 

[kray4660]

I have not used this function before, but looking at the help for it. I think you would want to start and end each compare with an "*", i.e., if lookslike({HCOMMENT.CLOB},"*?/?/??") then date({wlcerti.dissuedate}).

Also I that with a little creative thought you might be able to reduce the number of 'if's you are using.

 

[lbass]

What is the logic here? It sounds like you are saying if there is any date in the string, show the {wlcerti.dissuedate}.

Here's one way to get the date out:

stringvar x := {table.string};
stringvar array y := split(x," ");
numbervar i;
numbervar j := ubound;
datevar n := date(0,0,0);
for i := 1 to j do(
if isnumeric(y[1]) and
not(":" in y) then
if date(y) <> date(0,0,0) then
n := date(y)
);
n

-LB

 

[Oscar115]

lbass,
You are correct, I do not want to return {wlcerti.dissuedate} but rather the date within the string.

I have tried as you have suggested but am receiving an error "Bad Date format string" for date(y) when refreshing the report.

Any suggestion for getting past this?

I do appreciate your direction.

Thanks

 

[Oscar115]

I have modified your suggestion and have received the results I was hoping for.

I have changed it as follows:

stringvar x := {HCOMMENT.CLOB};
stringvar array y := split(x," ");
numbervar i;
numbervar j := ubound;
datevar n := date(0,0,0);
for i := 1 to j do(
if isnumeric(y[1]) and
not(":" in y) then
if isdate(y) = True then
n := date(y));
n

Thanks for your help!!
Oscar

 

[lbass]

Glad you worked it out.

-LB