Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to convert the Date column to DATE format.
I tried the below code but format function Date# is not working for second record.
Please advise.
Data:
load * ,DATE(DATE#(Date#(Date,'YYYY/DD/MM'),'YYYY/MM/DD')) AS NEW_DATE;
load * inline [
Sno,Date
1,2010/31/01
2,2011/02/28
];
I guess it would not take much of your time - to check if that works or not. Just try yourself like:
Data:
load * ,DATE(If( Isnum(Date#(Date,'YYYY/DD/MM')),Date#(Date,'YYYY/DD/MM'),DATE#(Date,'YYYY/MM/DD'))) AS NEW_DATE;
load * inline [
Sno,Date
1,2010/31/01
2,2011/02/28
];
Did it work? Please let know.
What do you want to achieve?
What would be the criteria to differentiate, whether the record is in format YYYY/DD/MM or in format YYYY/MM/DD?
The only chance (but not failproof) would be to identify a Daynumber > 12, but this will not help with entries like 2017/03/04.
May be this?
Data:
LOAD Sno, Date, Alt(Date(Date#(Date,'YYYY/MM/DD'),'YYYY/MM/DD'), Date(Date#(Date,'YYYY/DD/MM'),'YYYY/MM/DD')) as NEW_DATE;
load * inline [
Sno,Date
1,2010/31/01
2,2011/02/28
];
2010/31/01 and 2011/02/28 are two dates with different formats. I want to correct this data issue without using Alt function to valid date format.
Hope, I explained the issue clearly.
Hi loveisfail
I want to check the solution without using Alt function.
.... and how do you think a differentiation can be made?
I don't think so Can i know the reason to avoid the Alt() function. We can achieve by help of two inner joins. But, I don't recommend to use that. How we know till how many dates are there. I would always go for Alt() only this case
I agree with you
You may think about a solution like
MAKEDATE(LEFT(MyDate, 4), IF(MID(MyDate, 6,2) > 12, RIGHT(MyDate, 2), MID(MyDate, 6, 2), IF(MID(MyDate, 6, 2) > 12, MID(MyDate, 6, 2), RIGHT(MyDate, 2)) AS NewDate
However, the question remains, what makes you sure to read entries like 2017/03/04 in the correct way?
Hi Arjun,
I think using alt is the finest way of converting diff date formats into single format
Data:
LOAD Sno, Date, Date(Alt(Date#(Date,'YYYY/MM/DD'), Date#(Date,'YYYY/DD/MM'))) as NEW_DATE;
load * inline [
Sno,Date
1,2010/31/01
2,2011/02/28
];