How to Convert Date format

[madhubabum]

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,

[sunny_talwar]

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:

[robert_mika · Accepted solution]

Replace( Date_Field,'.','/') as Date_Field,

[Anonymous]

Hi Madhu,

Try

replace(Date_Field,'.','/')

[tresesco]

Small error of parenthesis. Correct one is:

Date(Date#(Date_Field, 'MM.DD.YYYY'), 'MM/DD/YYYY') as Date_Field

[sunny_talwar]

... I did see it when I was testing it

[sunny_talwar]

Found another one YYYY instead of YYY

[tresesco]

It's just a casual error ! happens to everybody.

[sunny_talwar]

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.

[tresesco]

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.