Format date include day name

[#1]

Hi all,

Anyone know how to convert or format this date: Wed, 01-July-2011 into this format: 01/07/2011. I have tried this expression:

=date(Date#([myDateField], 'ddd, DD-MMM-YYYY'),'DD/MM/YYYY') but can't work...

Anyone has any idea to do this? Any comments and suggestions are welcome.

Another problem is my database consists a variety of date with different format and those format are unexpected since my date date will always being updated... I am thinking a way to convert those unexpected date format into this format 'DD/MM/YYYY'. But I am not sure is it possible. Anyone has any ideas on this? Please let me know if my questions are not clear...

Thanks a lot.

[Anonymous]

Hi,

"Another problem is my database consists a variety of date with different format and those format are unexpected since my date date will always being updated"

You can use ALT function to add different format and convert them into date field. See below,

date1 = Wed, 01-July-2011

date2 = 01-July-2011, Wed

date3 = Wed, 01-July-11

ALT(Date#(Trim(SubField(date1, ' ',2)), 'DD-MMM-YYYY'),

       Date#(Trim(SubField(date2, ',',1)), 'DD-MMM-YYYY'),

       Date#(Trim(SubField(date3, ' ',2)), 'DD-MMM-YY'), 'No valid date' ) as Date_Field

[maxgro]

Set LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';

=date(date#(subfield('Wed, 01-July-2011', ' ',2), 'DD-MMMM-YYYY'), 'DD/MM/YYYY')

see here for MMMM

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/05/month-names

[#4]

HI cthesky87

you can override document settings to format the field in the number tab

1. Open properties and go to the number tab

2. Select field

3. Tick override document settings

4. Select date and use format pattern to suit you needs

Regards

Padma

[PDurnall]

=Date([Datefield],"WWW") worked great thank you 🙂