Are you able to right click the field in your Data window and "Change Date Type" to "Date"?
If not, you should be able to convert the integers into a relevant date format. Tableau forums have several relevant links:
and Google results for search... The good news is there is definitely a way.
I don't have the Change Data Type showing up. I tried the calculation specified in the link:
DATEADD('day',[Due Date] % 100 - 1,
DATEADD('month', INT(([Due Date] % 10000) / 100) - 1,
DATEADD('year', INT([Due Date] / 10000) - 1900,
and it returns an error with my SQL connection. Seems like the token #1900-01-01# causes problems. I'm sure there's a way...it's a matter of finding it. And since nobody uses my ERP system, I can't look over someone's shoulder.
My Custom SQL statement:
SELECT A0.ORDRMY AS A0ORDRMY, A0.STIDMY AS A0STIDMY, A0.QORDMY, A0.FIWHMY AS A0FIWHMY, A0.CONOMY, A0.QDEVMY, A0.YCMCMY, A0.QSPLMY, A0.SHPPMY, A0.UU40MY, A0.UUQ1MY, A0.REFNMY, A0.UUSAMY, A0.FITMMY, A0.FIDSMY, A0.ITRVMY, A0.MOSTMY, A0.QRCVMY, A0.QSCPMY, A0.CRUSMY, A0.CRDTMY, A0.SSDTMY, A0.ODDTMY, A0.HRRMMY, A0.RATOMY, A0.OPCRMY, A0.OPCPMY, A0.DYOSMY, A0.MSCSMY, A0.ISCSMY, A0.ASDTMY, B0.STIDAD, B0.ITNOAD, B0.ITRVAD, B0.SNOFAD, B0.NPWUAD, B0.VNDRAD, B0.ITYPAD, B0.INVFAD, B0.ITCLAD, B0.ITDSAD, B0.ANVAAD, B0.RTIDAD, B0.EGNOAD, C0.ITRVT9, B0.EDATAD, B0.UU25AD, C0.STIDT9, C0.ITNOT9, C0.ITRVT9, D0.ORDRMX, D0.OPSQMX, D0.NDETMX, D0.MSTNMX, D0.PFIDMX, D0.TBCDMX, D0.OPSTMX, D0.SCSZMX, D0.MSTYMX, D0.YCOCMX, D0.SSLHMX, D0.YCPCMX, D0.ASDTMX, D0.SRLHMX, E0.ORDRMY, E0.STIDMY, E0.QORDMY, E0.FIWHMY, E0.CONOMY, E0.QDEVMY, E0.YCMCMY, E0.QSPLMY, F0.WHIDWH, F0.STIDWH, F0.DESCWH, F0.PRPLWH, F0.UUCAWH FROM MOMASTVB A0 CROSS JOIN ITMRVAV0 B0 CROSS JOIN WHSMSTV0 F0 LEFT OUTER JOIN ITMSITV0 C0 ON B0.STIDAD=C0.STIDT9 AND B0.ITNOAD=C0.ITNOT9 LEFT OUTER JOIN MOROUTV0 D0 ON A0.ORDRMY=D0.ORDRMX AND A0.OPCRMY=D0.OPSQMX LEFT OUTER JOIN MOMASTVB E0 ON D0.ORDRMX=E0.ORDRMY WHERE (A0.STIDMY=B0.STIDAD AND A0.FITMMY=B0.ITNOAD AND A0.ITRVMY=B0.ITRVAD AND A0.FIWHMY=F0.WHIDWH) ORDER BY 1 DESC
How about stripping everything from your workbook except your date field, and post the packaged workbook file here so members can take a look?
In the future, just upload a packaged workbook with the extract embedded (file>>save as "Packaged workbook" -- .twbx file)
It looks like this is an 8 digit format, which is different from what you originally specified. So, do you want the end result to be
D’oh! I neglected to include that I added 20000000 to make the date YYYY. I really don’t care if it’s YYYYMMDD or MMDDYYYY – as long as I can use it in a chart to filter by.
(it’s obvious I have no idea what I’m doing)
OK--here you go. It was a multi-step process.
First, I converted your INT field to String--STR([Due Date])
From there, I used the LEFT, MID, and RIGHT string functions in the "DATE FINAL" calc to separate out the parts, as shown here:
The resulting calc is:
LEFT([STR Due Date], 4) // This covers the DD segment
+ "-" +
MID([STR Due Date], 5,2) // The MMM segment
+ "-" +
RIGHT([STR Due Date], 2) // The last two digits of YYYY
Does this work for you?
YES, that works great! I was so far out of the ballpark. The left, mid & right statements look exactly like those used in Excel.
Thanks again, you saved my day!!
You can use this formula, if the numeric date is in the format YYYYMMDD:
dateadd('year', int([Date] / 10000) - 1900,
dateadd('month', int(([Date] % 10000) / 100) - 1,
dateadd('day', [Date] % 100 - 1, #1900-01-01#)))
This formula is taken from here: Date Calculations | Tableau Software
Ps. YYYYMMDD as number is a recurring question:
Kettan's method will actually work best, as numbers evaluate much faster than strings.
Date conversions will soon (8.1) be much easier with the new DATEPARSE function as demonstrated in Live Stream of TCC13 Keynote | Tableau Software.
DATEPARSE function: A new function is available to easily convert a string from one format into a date field. This function is available for data sources that support the DATEPARSE function including Data Extracts, Postgres, Oracle, MySQL, and Excel.
I thought it would be nice to have all known techniques (string, math, schema.ini, wait for dateparse in 8.1) in place and therefore shared various techniques.
Although the Knowledge Base, Quick Start Guides and Video On Demand are good, I wish we also had a collection of frequently answered questions which made it both easier-for-learners-to-help-themselves and helpers-to-help. My wish is requested here: FAQ page.
The YYYYMMDD is definitely one of these as seen in
Absolutely agree--although, I often forget something I learned last week, so I don't mind repeating answers until I get it through my own skull. This thread is a good example--I have referred users to the schema.ini file in the past, but for whatever reason, I went into creating a string then turning that into a date. But, alas, it should be about the folks who need help and not the helpers, so I am all for more widely available answers and documentation.