Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try like that:
LOAD *, Date(Date#(left(Date,3)&'/'&right(Date,2),'MMM/YY'),'DD/MM/YYYY') as NewDate;
LOAD * INLINE [
Date
"Aug'17"
];
Let me ask this date field in string date or pure date let me know.
try like that:
LOAD *, Date(Date#(left(Date,3)&'/'&right(Date,2),'MMM/YY'),'DD/MM/YYYY') as NewDate;
LOAD * INLINE [
Date
"Aug'17"
];
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;
Hi Nick,
may be
Date(Date#(DateField,'MMM'&Chr(39)&'YY'),'DD/MM/YYYY')
Regards,
Antonio
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)
Another way also
Data:
LOAD *,
Date( Date#( PurgeChar(MonthYear,Chr(39)),'MMMYY'),'DD/MM/YYYY') as NewDates;
LOAD * Inline
[
MonthYear
Aug'17
];
simplest way to do it is below
monthstart(date#(Month,'MMM''YY')) as MonthStart
Thank you Frank. This works perfectly.
Thanks Antonio. This is great.