Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date format conversion

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.

9 Replies
fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi, try this:

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

Hope this helps.

Regards,

Fernando

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

Issue TypeKeySummaryAssigneeReporterPriorityStatusResolutionCreatedUpdatedSub-Tasks
A1aaabcdMediumBacklogUNRESOLVED7/4/2011 2:307/4/2011 2:31
B2bbbscdMediumClosedFixed6/20/2011 2:517/4/2011 2:30

This is what the data looks like.

Not applicable
Author

i am pulling it from excel

Not applicable
Author

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

Not applicable
Author

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