How to convert date format

Sun, 25 Sep 2011 05:59:24 GMT

Hello,All.

I have a column which is date information.

I need to convert it as below.

BEFORE(PRESENT)

Jan 3th 2011 12:00AM (MM DD YYYY hhhh AM)

AFTER(The date I would like to convert)

2011 01 03 (YYYY MM DD)

How can I convert on QlikView? Thanks.

its_anandrjs — Sun, 25 Sep 2011 06:50:21 GMT

Hi,

Use date command like

Date( Your Date Field , 'YYYY/MM/DD' ) as Newfield

Rgds

Anand

Re: How to convert date format

swuehl — Sun, 25 Sep 2011 10:24:30 GMT

Hi atsushiikari,

before your could apply the date() function to format your dates,

you need to ensure that your date field is recognized as such by QlikView.

I assume your Date information field will not be recognized by QlikView,

mainly because you are using something like an ordinal character extension

for days like 1st, 2nd, 3rd, 4th, is this correct?

So I believe you need to parse your date field in before applying the Date formatting, maybe something like this

(Instead of 'Jan 3th 2011 12:00AM' , put in your FIELDNAME into the subfield parameter):

=date(

date#(

subfield('Jan 3th 2011 12:00AM',' ',1)

&'/'& keepchar(subfield('Jan 3th 2011 12:00AM',' ',2),'0123456789')

&'/'& subfield('Jan 3th 2011 12:00AM',' ',3)

,'MMM/DD/YYYY')

,'YYYY MM DD')

Regards,

Stefan

edit: above expression is for use in a chart, you could use similar in the script:

LOAD

.....

date( date#( subfield(DATEFIELD,' ',1) &'/'& keepchar(subfield(DATEFIELD,' ',2),'0123456789')

&'/'& subfield(DATEFIELD,' ',3) ,'MMM/DD/YYYY') ,'YYYY MM DD') as NEWDATEFIELD,

...

FROM ...;