How can i Extract Date from the field (ha29-07-87rish )?

[#1]

i have a column containing data like below

ha29-07-87rish

how can i extract date from the above column

[tamilarasu]

Hi Srikanth,

Try,

Date(Date#(KeepChar(FieldName,'0123456789-'),'DD-MM-YY'))

Or

Date(Date#(KeepChar(FieldName,'0123456789-'),'DD-MM-YY'), 'Expected Format')

[Chanty4u]

try this

a:

load * Inline [

Date

ha29-07-87rish

];

Result:

LOAD * ,

date(Date#(PurgeChar(Date,'harish'),'DD-MM-YYYY') ) AS new Resident a;

[maxgro]

Date(date#(keepchar('ha29-07-87rish', '0123456789'), 'DDMMYY'))

[#5]

  Hi Thanks All

02-06-14*B

B09-10-12DFG

2125-11-13253

5ma03-10-85he_sh

ha29-07-87rish

I need A genric Function to Extract the Date From the Field Data to DD/MM/YY format?

[maxgro]

X:

load

  field,

  date(date#(mid(field, index(field, '-')-2, 8), 'DD-MM-YY'), 'DD/MM/YY') as datefield

inline [

field

02-06-14*B

B09-10-12DFG

2125-11-13253

5ma03-10-85he_sh

ha29-07-87rish

];

[MarcoWedel]

Hi,

one generic solution might be also:

SET vIsLet    = (Upper($1)<>Lower($1)); 
SET vIsNumLet = (Upper($1)<>Lower($1) or IsNum($1)); 

mapChar2Form: 
Mapping LOAD char, If(IsNum(char),'0','A') Where $(vIsNumLet(char)); 
LOAD Chr(RecNo()) as char AutoGenerate 65535;

SET DateFormat='DD/MM/YY';
SET vDatFormSrc = 'DD-MM-YY'; //only numerical month formats (i.e. M or MM)!
LET vDatFormSearch = Replace(MapSubString('mapChar2Form',vDatFormSrc),'A','0');

table1:
LOAD *,
    Date(Date#(Mid(String,Index(MapSubString('mapChar2Form',String),'$(vDatFormSearch)'),Len('$(vDatFormSrc)')),'$(vDatFormSrc)')) as Date
INLINE [
    String
    02-06-14*B
    B09-10-12DFG
    2125-11-13253
    5ma03-10-85he_sh
    ha29-07-87rish
    AB-CD01-02-03EF-GH
    -02-03-11-A-B-C
    A-02-03-11-A-B-C
    2126-011-13253
    2127-11-1A3253
    B09-10-A12D04-05-12FG
    A1/2/10B/12/C
    A03/04/14B/12/C
    A2-3-11B-13-C
];

while

SET vDatFormSrc = 'D/M/YY';

using the same strings would deliver this result:

see also:Finding text patterns in Data Columns

hope this helps

regards

Marco

[Anonymous]

Hi Srikanth,

If your mentioned date field is in standard format then you can extract date from following way:

tabletest1:

LOAD * INLINE [

    date_test

    ha29-07-87rish

];

tabletest2:

load date_test,

Mid(date_test,3,8) as FinalDate

Resident tabletest1;

drop Table tabletest1;

Result of above will be as following:

29-07-87