topic Re: How to Convert Date format in QlikView

How to Convert Date format

madhubabum — Tue, 21 Jul 2015 12:51:21 GMT

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,

Re: How to Convert Date format

sunny_talwar — Tue, 21 Jul 2015 12:53:16 GMT

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:

Re: How to Convert Date format

robert_mika — Tue, 21 Jul 2015 12:54:12 GMT

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

Re: How to Convert Date format

Anonymous — Tue, 21 Jul 2015 12:54:43 GMT

Hi Madhu,

Try

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

Re: How to Convert Date format

tresB — Tue, 21 Jul 2015 12:55:52 GMT

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

sunny_talwar — Tue, 21 Jul 2015 12:57:41 GMT

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

Re: How to Convert Date format

sunny_talwar — Tue, 21 Jul 2015 12:59:31 GMT

Found another one YYYY instead of YYY

Re: How to Convert Date format

tresB — Tue, 21 Jul 2015 13:01:38 GMT

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

Re: How to Convert Date format

sunny_talwar — Tue, 21 Jul 2015 13:03:38 GMT

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.

Re: How to Convert Date format

tresB — Tue, 21 Jul 2015 13:07:17 GMT

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.