Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
madhubabum
Contributor

How to Convert Date format

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,

1 Solution

Accepted Solutions

Re: How to Convert Date format

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

View solution in original post

9 Replies

Re: How to Convert Date format

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:


Capture.PNG

Re: How to Convert Date format

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

View solution in original post

niharika
Contributor II

Re: How to Convert Date format

Hi Madhu,

Try

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

MVP
MVP

Re: How to Convert Date format

Small error of parenthesis. Correct one is:

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

Re: How to Convert Date format

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

Re: How to Convert Date format

Found another one YYYY instead of YYY

MVP
MVP

Re: How to Convert Date format

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

Re: How to Convert Date format

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.

MVP
MVP

Re: How to Convert Date format

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.