date format conversions

[Anonymous]

Hi All,

I am trying to convert the following format:

Date :  Sep 2017 to 08-2017 

Any suggestions please?

Regards

[YoussefBelloum]

hi,

try this on the script:

num(month(date#(left(Date,3),'MMM')))&'-'&right(Date,4) as Date

[Anonymous]

Nice one Youssef, its picking the 2017 field but not picking the month field.

Extract is looking like : -2017 (Month is not picked)

Regards

[bhargav_bhat]

Hi Rudra,

Maybe this will work

Date(Date#('Sep 2017','MMM YYYY'),'MM-YYYY')

Regards,

Bhargav

[Anonymous]

Hi Bhargav, tried that one, but its not working.

Regards

[bhargav_bhat]

Hi Rudra,

Sep 2017 is in Date format or text format

Regards,

Bhargav

[Anonymous]

Bhargav , its in Text Format

Regards

[bhargav_bhat]

Hi Rudra,

The above expression should work .Can you please share Sample data if possible

Regards,

Bhargav

[bhargav_bhat]

You can also try this expression if above expression does'nt work

Pick(Match(Left(FieldName,3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

,01,02,03,04,05,06,07,08,09,10,11,12) & '-' & right(FieldName,4)

[Anonymous]

Hi Bhargav, i can explain the scenario:

* I got no date field in source data.

* I picked the date part from source data excel file names(File name has extension -sep 2017.xls )

* Used text between to extract that part (Sep 2017)

* Now i am trying to convert that Sep 2017 field value to 08-2017 to mark it as date field in all the excel source files.

Regards