Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a column containing data like below
ha29-07-87rish |
how can i extract date from the above column
Hi Srikanth,
Try,
Date(Date#(KeepChar(FieldName,'0123456789-'),'DD-MM-YY'))
Or
Date(Date#(KeepChar(FieldName,'0123456789-'),'DD-MM-YY'), 'Expected Format')
try this
a:
load * Inline [
Date
ha29-07-87rish
];
Result:
LOAD * ,
date(Date#(PurgeChar(Date,'harish'),'DD-MM-YYYY') ) AS new Resident a;
Date(date#(keepchar('ha29-07-87rish', '0123456789'), 'DDMMYY'))
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?
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
];
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
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