The issue here is that the Invoice Date is not being recognized as a date field.
Create a calculated field similar to the following:
date(mid([Invoice Date], 5, 2) + "/" + right([Invoice Date], 2) + "/" + left([Invoice Date], 4))
Use this calculation as your date field. Then, you can format it so that is yyyy mm dd
Hope this helps!
I tried your tips, but it failed on my side which shows 12/30/1899.(http://www.evernote.com/shard/s220/sh/fe060b24-e486-4cd0-a6db-bc841ef4b389/950a5b9e905799e028c677081fd057e2)
I am not sure if it is because my version is 8.3.
Could you give me advice?
Did you try DATEPARSE function? Availability depends on the database but if you create an extract you will have it regardless of the database the original data resides in. You can pass the date format string to dateparse function.
Here is the relevant documentation
It looks like the field is coming in as a date already, but might not be parsed correctly. Right click invoice date and select on the side bar and select change format, then string. That will convert it to a string value. Then you can apply the dateparse function in a calculated field:
Note, case is important as lower case M is used for minutes.
FYI. The format you selected above has to do with the display of a date, rather than the structural understanding of it. If the instructions above don't work, check your raw data and make sure the date is indeed coming in as yyyyMMdd format. It is possible these dates are being parsed correctly already, but that you are overriding it when you display them in Tableau.
Let me know if this helps,
I read the "Understanding the DATEPARSE Function" , but,it does not work on my side.
As I use SQLite data, the article does not mention that DATEPARSE Function is adopted in SQLite.
Do you think SQLite is the main issue?
Tableau doesn't support DATEPARSE on SQLite. If you would like to use DATEPARSE, you can create a Tableau Extract from the data. Alternatively you can write your own calculated field to parse the date against live SQLite connection.
will give you February 11, 2005 for example.
DATE(MID('[Invoice Date]',5,2)+'/'+RIGHT('[Invoice Date]',2)+'/'+LEFT('[Invoice Date]',4))
I don't have SQLite handy so I am not sure if there's a LEFT/RIGHT but I am certain that it has a substr function so if the one above doesn't work please try
DATE(MID('[Invoice Date]',5,2)+'/'+MID('[Invoice Date]',7,2)+'/'+MID('[Invoice Date]',1,4))