Extract Date from nvarchar field 1

[daguas]

Hi I need to extract the date portion of a nvarchar field with the data in the field entered as 'Integrated -0 1/03/06" any ideas?
Thanks

 

[synapsevampire]

Use the instrrev function, as in

whileprintingrecords;
stringvar MyStrDate:=mid({table.field}, instrrev({table.field}," ")+1)

If you want to convert it to a date type, then you need to use the mid, as in:

whileprintingrecords;
Stringvar MyDt:="blah blah blah 1/2/06";
stringvar MyStrDate:=trim(mid(MyDt, instrrev(MyDt," ")));
cdate(MyStrDate)

Replace MyDt with your table.field

-k

 

[daguas]

Thanks that took care of it.

 

[synapsevampire]

No worries, and give your dba a swift kick somewhere not so pleasant for me

-k

 

[daguas]

k,
a little more help on conveting it to a date type. My formula is
whileprintingrecords;
Stringvar MyDt:={MyTable.Field}";
stringvar MyStrDate:=trim(mid(MyDt, instrrev(MyDt," ")));
cdate(MyStrDate)

It give the error Bad Date Type.

 

[synapsevampire]

Sounds like the date isn't consistent in the field.

Try:

a little more help on conveting it to a date type. My formula is
whileprintingrecords;
Stringvar MyDt:={MyTable.Field}";
stringvar MyStrDate:=trim(mid(MyDt, instrrev(MyDt," ")));
if isdate(MyStrDate) then
cdate(MyStrDate)
else
cdate(1970,1,1)

Take a look at what is in the field for the ones that are returned as 1/1/1970 by placing your field alongside in the details.

-k

 

[daguas]

Looks like the date conversion formula is always failing the IsDate test. The first date displayed is the first formula you gave my which does extract the date as a string. the second date is the formula to convert to date and the third is the acutal field entry.
i.e
String Formula Date Formula Actual Field
1/10/2006 1/1/1970 Integrated - 1/10/2006
Any thoughts

 

[lbass]

I justed tested SV's formula and it works. I wonder if your memo field really has the space before the 01. In your original post, it looked like the hyphen was immediately followed by the 01, in which case the formula would extract -01/10/06 and would fail. If there is always a hyphen, you could try:

trim(mid({table.field},instrrev({table.field},"-")+1)

-LB

 

[synapsevampire]

Yeah, test LB's theory.

You may not be demonstrating the field properly.

Another check would be to use:

whileprintingrecords;
numbervar fldlen:= len(trim({table.field}));+1
Stringvar Output:="";
for x:= 1 to len({table.field} do(
if isnumeric(mid({table.field},fldlen-x,1))
or
mid({table.field},fldlen-x,1)) = "/" then
Output := Output+mid({table.field},fldlen-x,1))
);
Output

This assumes that there are no other numerics or slashes (/) in the field. If there are, then you'll need to stop the output fill in an else clause.

-k