topic Re: Date format conversion in QlikView

Date format conversion

Tue, 05 Jul 2011 20:45:36 GMT

Hi All,

how can i convert DD/MMM/YYYY format into MM/DD/YYYY in QV? What would my expression look like?

ex: my date field looks like this: 02/Jun/2011 or 05/Jul/2011

The month is always 3 letters.

My set statement is SET DateFormat='M/D/YYYY' --- which i need for the other queries.

So can i use another SET statement just before running this query (which i tried and didnt work) or can i convert the format using an expression?

Thanks.

Date format conversion

fosuzuki — Tue, 05 Jul 2011 20:58:16 GMT

Hi, try this:

=date(date#('01/fev/2011', 'DD/MMM/YYYY'), 'DD/MM/YYYY')

Hope this helps.

Regards,

Fernando

Date format conversion

Tue, 05 Jul 2011 21:03:28 GMT

still nothing is being displayed in the list box with the expression you provided.

i replaced it like this:

date(date#([Create Date], 'DD/MMM/YYYY'), 'DD/MM/YYYY')

[Create date] is my field where all dates are stored.

Re: Date format conversion

Tue, 05 Jul 2011 21:20:44 GMT

Hi there,

Did you check whats the date format after you have loaded the data into the QVW application?.

Anyways, I have tried using an excel(attached with this post) where I have used the date format

as your original data (for e.g. 02-Jun-11).

So when I load it in QV, it will read it as M/D/YYYY as per the set format in the main tab of the script editor.

Then(please find attached) I have formated it this way,

Say your date field is called "create date" or [create date],

then

Date([create date],'MM/DD/YYYY')

Hope this helps,

Thanks,

Bikash

Date format conversion

Tue, 05 Jul 2011 21:31:14 GMT

Yes, i checked the format after its being loaded into qlikview. its DD/MMM/YY and now i need to convert it to MM/DD/YYYY

and my list box isnt displaying anything after trying your method.

Date format conversion

Tue, 05 Jul 2011 21:39:09 GMT

Hi,

May i ask whats your data source?. If its in email compatible format for example Excel or CSV,

will you be able to post a sample data set so that we can try.

Thanks,

Bikash

Date format conversion

Tue, 05 Jul 2011 21:53:20 GMT

Issue Type	Key	Summary	Assignee	Reporter	Priority	Status	Resolution	Created	Updated	Sub-Tasks
A	1	aa	ab	cd	Medium	Backlog	UNRESOLVED	7/4/2011 2:30	7/4/2011 2:31
B	2	bb	bs	cd	Medium	Closed	Fixed	6/20/2011 2:51	7/4/2011 2:30

This is what the data looks like.

Date format conversion

Tue, 05 Jul 2011 21:53:49 GMT

i am pulling it from excel

Date format conversion

Tue, 05 Jul 2011 21:59:41 GMT

But isn't your Create Date already in M/D/YYYY format?

Anyways if you still have problem

you can try this

Date(floor([Create Date],'MM/DD/YYYY'))

As you have time stamp too, which might be causing some issue.

Thanks,

Bikash

Re: Date format conversion

Tue, 05 Jul 2011 22:08:52 GMT

Hi ggoyal_123,

Here's your solution.

=Date(Date#(left([Create Date],11),'DD-MMM-YYYY'),'DD/MM/YYYY')

Also find attached the sample solution. it worked for me.

Thanks,

Bikash