want to change (month-year) fromate into dd-mm-yyyy) formate

[yadav_anil782]

Dear All ,

i have Period_name column in my source table  but date format is different i wants to change  in( dd-mm-yyyy) format . plz give reply i can anyone solve this issue -

source sheet i attached 

[sunny_talwar]

Not sure what you want to see for something like this -> Mar_Adj-12?

[SreeniJD]

Hi Anil,

If you don't have date in your period_date, it would be difficult to get date, but you get the format like...

=Date(date#(PERIOD_NAME,'MON-YY'),'MM/YYYY')

Happy to help!

Sreeni

[yadav_anil782]

HI Sunny ,

some dates coming with adjustment period and those date i need to calculate with given current month

suppose   if date  is Mar_Adj-12 then i need to convert  in "01-03-2012"

[SreeniJD]

Anil,

I recommend, cleanse the data while loading as we don't have any direct approach.

@sunny t - please correct me.

Regards,

Sreeni

[sunilkumarqv]

Try this

Date(Replace(Replace(Replace(PERIOD_NAME,'_Adj','-1'),'Mar',3),'-','/'),'DD-MM-YYYY') as PERIOD_Date,

LOAD

   PERIOD_NAME,

Date(Replace(Replace(Replace(PERIOD_NAME,'_Adj','-1'),'Mar',3),'-','/'),'DD-MM-YYYY') as PERIOD_Date,

     ACCOUNT_CODE,

     BEGIN_CR,

     BEGIN_DR,

     JE_CATEGORY,

     JE_SOURCE,

     LEDGER_ID,

     LEDGER_NAME,

     PERIOD_CR,

     PERIOD_DR,

     PERIOD_NUM,

     PERIOD_YEAR,

     SEGMENT5_DESC

FROM

[DATE FORMATE .xlsx]

(ooxml, embedded labels, table is TB01_20150924_120040);

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD If(IsNum(PERIOD_NAME), PERIOD_NAME, Date(Date#(Replace(PERIOD_NAME, '_Adj',''), 'MMM-YY'))) as Date

FROM

[DATE FORMATE .xlsx]

(ooxml, embedded labels, table is TB01_20150924_120040);

[Digvijay_Singh]

Something like  Date(MonthStart(Date#(purgechar(PERIOD_NAME,'_Adj'),'MMM-YY')),'MMM-YY') as Period_Date

[tresesco]

Try like:

Load *,
Date(Alt(Date#(PERIOD_NAME, 'MMM-YY'),Date#(PERIOD_NAME, 'MMM_Adj-YY')), 'DD-MMM-YY') as NewDate
Inline [
PERIOD_NAME
Mar-10
Mar_Adj-12
Mar_Adj-12
Jun-12
Jun-12
Mar_Adj-13
Mar-10
Mar_Adj-12

]

[yadav_anil782]

Dear Sunil,

thanks  for help but its not working , its showing blank