I have first changed string type to date type and then right clicking the object you can find "date format" in Field properties where you can select your desired date format or also custom date format as per requirement. Once this is done Tableau is able to read the date correctly. Please find the attached document.
DateFormat.docx 744.3 KB
But actually in my database, in a single date column there are two types of format & when i changed the format directly then it change the values like 20.01.2011 to 01/20/2011 but not able to change 08.01.2011 to 01/08/2011(it display 08/01/2011). This is only one format but another format is "yyyy/mm/dd" ( which are already dates).
DATE 03.01.2012, 14:38:12.000 08.01.2012, 18:09:14.000 19.12.2011, 15:46:20.000 20.12.2011, 20:23:22.000 2011/12/16 12:11
and what i want is like this: -
CHK_DATE 12/16/2011 12/19/2011 12/20/2011 1/3/2012 1/8/2012
& when i simply changed the format from string to date then it gives me
CHK_DATE 12/16/2011 12/19/2011 12/20/2011 3/1/2012 8/1/2012
Values which are bold is wrong.
You have a string in a date column in your database?
Do you mean that you have different date formats in a string/char column in your database?
Try this (assuming your date/string variable is called [YOUR DATE]):
Create a new variable, [TMP DATE] with the following code:
IF (RIGHT(LEFT([YOUR DATE], 3), 1)) = "." THEN RIGHT(LEFT([YOUR DATE], 10), 4) + "/" + RIGHT(LEFT([YOUR DATE], 5), 2) + "/" + LEFT([YOUR DATE], 2) ELSE [YOUR DATE] END
Then create another variable [CORRECT DATE] with the following code:
Then use [CORRECT DATE] in your report.
Thank you so much Martin,
It worked. This formula gives me what i wanted.