Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi, try this:
=date(date#('01/fev/2011', 'DD/MMM/YYYY'), 'DD/MM/YYYY')
Hope this helps.
Regards,
Fernando
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.
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
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.
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
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.
i am pulling it from excel
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
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