Hi Kirk,

I could not duplicate your problem using a text data source or a calculated field as the source of the string, I always got the correct results. What data source are you using?

Jonathan

I learned something new today. Tableau's DATE() function works differently depending on whether the source data is text or an extract. I created a text file with a variety of formats then imported it into Tableau as a text file, and also as an extract. Here are the records:

ID Date

1 10JAN12

2 11Jan12

3 12jan12

4 13JAN2012

5 14Jan2012

6 15jan2012

You can see the results of the calculations in the "start here" dashboard. The original calculation you created works fine for any of these formats for the text data source, but returns Nulls or even a different interpetation of the data for the extract. With that information in hand, I created a "Working Calculation" field with the following formula that works for both DDMMMYYYY and DDMmmYYYY formats:

DATE(LEFT([DateString],2) + "-" + MID([DateString],3,1) + LOWER(MID([DateString],4,2)) + "-" + RIGHT([DateString],4))

I'm submitting this to Tableau Tech Support, at the very least this behavior should be documented somewhere, if not made the same across data sources.

Jonathan

You're welcome!

You're welcome, Kirk!

Also, I found out from Tableau tech support that (at least for the current version 7.0.4) it is expected behavior that the DATE() function works differently for text files vs. extracts.

Jonathan

Hi Jonathan,

I have another related question perhaps you have advice based on this string of work.  I have two fields Admit Date and Admit Time.  When I combine them using a calculated field to create a DateTime field I can see the discrete data when using a live connection, but the calculation performs differently on an Extract.  On an extract it only shows the time at midnight. 

I've attached a sample workbook highlighting the difference. 

Any thoughts?

Ted

Hi Ted,

I have any advice when I saw your question, but now I do. It turns out that given a datetime and an Excel data source the STR() function returns just the date for fields with a time of 12:00:00 AM and just the date for fields with a date of 12/30/1899. Whereas for an extract, given a datetime the STR() function returns the entire datetime. I suspect this is due to Tableau using the JET engine for Excel/Access/text and its own code for the extract, but I could be wrong.

In your workbook, I started by taking the DATETIME off the Admit Date Time Extract field to create the Admit Date Time Extract String (original) field. We can see that the field is returning the original time for the Admit Date along with the time for the Admit Time field, so we can now understand why the function was failing to produce expected results.

Therefore, what I did was wrapped the Admit Date inside the DATE() function, so the STR() function now looks like STR(DATE([Admit Date])) and that gets us a working string, seen in the Admit Date Time Extract String (updated) field and the same formula works to get a DATETIME value, as seen in the Admit Date Time Extract (updated) field. The good news is that the updated function will work for both types of data sources.

Jonathan

Fantastic.  Thanks for your very quick reply, that does indeed do what I wanted.  I knew it had to be something simple, but I never tried that. 

You're welcome! I asked Tableau tech support to document the issue with DATE() behaving differently depending on the data source, I'll also ask them with regards to the STR() function so hopefully it won't come up again.

Jonathan