Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have the following date format (MM.DD.YYYY) , Here i need to Change the Separator from '.' To '/' (How to get this)
Ex:
Date_Field,
6.25.2010,
1.29.2011,
12.21.2013,
Needed OutPut :
Date_Field,
6/25/2010,
1/29/2011,
12/21/2013,
Replace( Date_Field,'.','/') as Date_Field,
Try this:
Date(Date#(Date_Field), 'MM.DD.YYYY'), 'MM/DD/YYY') as Date_Field
Date(Date#(Date_Field, 'MM.DD.YYYY'), 'MM/DD/YYYY') as Date_Field
Script:
Table:
LOAD *,
Date(Date#(Date_Field, 'MM.DD.YYYY'), 'MM/DD/YYYY') as New_Date_Field;
LOAD * Inline [
Date_Field
6.25.2010
1.29.2011
12.21.2013
];
Output:
Replace( Date_Field,'.','/') as Date_Field,
Hi Madhu,
Try
replace(Date_Field,'.','/')
Small error of parenthesis. Correct one is:
Date(Date#(Date_Field, 'MM.DD.YYYY'), 'MM/DD/YYYY') as Date_Field
... I did see it when I was testing it
Found another one YYYY instead of YYY
It's just a casual error ! happens to everybody.
I agree, but happens to me more often than not. I am better off testing things rather than just answering them top of my head because I make way to much silly mistakes when it is from top of my head.
This way you get the right output technically - true. However, the approach should not be recommended. This way you are making the date field a string rather than a dual (numeric at the backend). Thus, this field would fall sort in the front-end for any date function direct use or so. Better to use Date(Date#(... as shown above.
Hope that makes sense.