How to convert date 1110101 which means 1/1/2011 to a date that Tableau can recognize

Question

I have a data set with date format (which according to our IT it's an IBM format) 1110101 which means 1/1/2011, so 1160506 means 5/6/2016. I tried to convert it to date format that tableau can recognize and here's the plan:

Step 1, I create a field called "Date Converted"= Date I have + 19000000 Step 2, I use DATEPARSE to convert it to date by:

DATEPARSE("yyyymmdd",19000000+[Date I have])

But Tableau doesn't accept DATEPARSE and it says it's an unknown syntax. Anyone know's how to correctly convert IBM date format to a date that Tableau can recognize?

What data source are you connecting to? Some Tableau functions are not available for some data sources (DATEPARSE for SQL Server for example) — Alexander
Is your [Date I have] field a string or integer? The "+" operator does different things accordingly. If your data source does not have DateParse(), or MakeDate(), you can always create a string in MM/DD/YYYY format and use the Date() function — Alex Blakemore

Mehul Katara Mehul Katara · Accepted Answer · 2017-06-06T05:49:37

My date format

Change your format in string

Create custom calculation

MID([Date],6,2) + "/" + MID([Date],4,2) + "/" +MID([Date],2,2)

Change your data type to date

You date will look like this

How to convert date 1110101 which means 1/1/2011 to a date that Tableau can recognize

1 Answers