make date

[#1]

How can you make date from a following field

PerformanceLog_201103206565656565.tmp

How do you extract:  20110320  as date from above in (YYYYMMDD) format.

Thanks

[Anil_Babu_Samineni]

How about this:

LOAD *,

Date#(KeepChar(Field,'0123'),'YYYYMMDD') as Field1

INLINE [

Field

PerformanceLog_201103206565656565.tmp

];

[kaushiknsolanki]

Hi,

Try this.

Load *,Date(Date#(Mid(Data,Index(Data,'_')+1,8),'YYYYMMDD')) as DATE inline [

Data

PerformanceLog_201103206565656565.tmp

];

Regards,

Kaushik Solanki

[Anil_Babu_Samineni]

Kaushik, How about This?

LOAD *,

mid(Field,16, 8 ) as Field1

INLINE [

Field

PerformanceLog_201103206565656565.tmp

];

[tamilarasu · Accepted solution]

Hi Hanns,

You can also try,

Date(Date#(left(Subfield('Field', '_',2),8),'YYYYMMDD'),'YYYYMMDD') as Date

[kaushiknsolanki]

Hi Anil,

The expression is right, however whenever the text character changes, the script you wrote will fail.

So it is important to get the dynamic value. Have a look at the expression I gave, which is dynamic in nature.

Regards,

Kaushik Solanki

[MarcoWedel]

Hi,

another solution could be:

table1:
LOAD *,
    Date#(Left(SubField(filename,'_',-1),8),'YYYYMMDD') as date
INLINE [
    filename
    PerformanceLog_201103206565656565.tmp
    PerfLog_201204216565656565.tmp
    Performance_Log_201305226565656565.tmp
];

hope this helps

regards

Marco