How to create dates from Month-Year Strings?

[nickjose7]

kush141087‌ serj_shu‌ mdmukramali‌ antoniotiman‌

Hi,

I need to create dates from a Month field having values like: Aug'17

Desired output: 01/08/2017

Any help would be highly appreciated

Thanks

Nick

[its_anandrjs]

Let me ask this date field in string date or pure date let me know.

[Frank_Hartmann · Accepted solution]

try like that:

LOAD *, Date(Date#(left(Date,3)&'/'&right(Date,2),'MMM/YY'),'DD/MM/YYYY') as NewDate;

LOAD * INLINE [

    Date

    "Aug'17"

];

[its_anandrjs]

If you have any sample so share that, you have to use the Makedate function in your load script and break the Year Month from the existing Month Name string see my example and also the attached. You can do this in the single table also.

Data:

LOAD *,

Month(Date#(Left(MonthYear,3),'MMM')) as Month,

Year(Date(Date#( Right(MonthYear,2),'YY'),'YY')) as Year;

LOAD * Inline

[

MonthYear

Aug'17

];

NewData:

LOAD *,

MakeDate(Year,Month,1) as NewDate

Resident Data;

DROP Table Data;

[antoniotiman]

Hi Nick,

may be

Date(Date#(DateField,'MMM'&Chr(39)&'YY'),'DD/MM/YYYY')

Regards,

Antonio

[Sergey_Shuklin]

Hello, Nick!

You can use MakeDate() and Left()/Right() functions to create dates from strings:

Expression:

=MakeDate(20&Right(mnt,2),

Pick(Match(left(mnt,3),

'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),

1,      2,       3,       4,      5,      6,      7,      8,       9,    10,    11,    12),

1)

[its_anandrjs]

Another way also

Data:

LOAD *,

Date( Date#( PurgeChar(MonthYear,Chr(39)),'MMMYY'),'DD/MM/YYYY') as NewDates;

LOAD * Inline

[

MonthYear

Aug'17

];

[Kushal_Chawda]

simplest way to do it is below

monthstart(date#(Month,'MMM''YY')) as MonthStart

[nickjose7]

Thank you Frank. This works perfectly.

[nickjose7]

Thanks Antonio. This is great.