how to extract date from filename

[#1]

i have a doubt can you clarify me i have a data in which i have to retrieve date from excel name i have done using

left(right(filenmae)2)1)

but i have doubt if have date in 29-jan-2016 how can i get date without using left and right function how ever the data may be i should get it dynamicly

[MK_QSL]

What is the filename?

[#3]

MAC_R2_CR_PBTV_ACCOUNTS_04-july-2016

[MK_QSL]

LOAD

     Date(Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY')) as Date

FROM

YourFile

or

LOAD

     Date(Date#(SubField(FileBaseName(),'_',-1),'DD-MMMM-YYYY')) as Date

FROM

YourFile

[#5]

thanks Manish i have doubt i want to retrieve date dynamically what ever the format the date is like

MAC_R2_CR_PBTV_ACCOUNTS_04-july-2016

MAC_R2_CR_PBTV_ACCOUNTS_04-07-2016

[MK_QSL]

LOAD

  Date(Alt(

     Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY'),

     Date#(SubField(FileBaseName(),'_',-1),'DD-MMMM-YYYY'),

     Date#(SubField(FileBaseName(),'_',-1),'DD-MM-YYYY')

     )) as Date

FROM

[MAC_R2_CR_PBTV_ACCOUNTS_*.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#7]

no Manish i am asking the file name may be either like this

MAC_R2_CR_PBTV_ACCOUNTS_04-july-2016

or

MAC_R2_CR_PBTV_ACCOUNTS_04-07-2016

however it is it should work in a single expression

[MK_QSL · Accepted solution]

Use this.... to extract date field... it should work..

Date(Alt(

     Date#(SubField(FileBaseName(),'_',-1),'DD-MMM-YYYY'),

     Date#(SubField(FileBaseName(),'_',-1),'DD-MMMM-YYYY'),

     Date#(SubField(FileBaseName(),'_',-1),'DD-MM-YYYY')

     )) as Date

[#9]

great manish tanks for your help

[MK_QSL]

If you have got the correct solution, kindly close the thread by selecting correct answer.. this will help others having same issue.