1 2 3 Previous Next 43 Replies Latest reply on Oct 14, 2015 2:03 AM by kettan

Error in Date Interpretation

Andreas Jagdhuber Oct 9, 2015 1:37 AM

I need some help here:

I have a database with a column of dates in the format "dd.mm.yyyy hh:mm:ss". A standard format, to the best of my understanding.

When connecting Tableau to the database, it interprets the dates wrongly, e.g. "30.12.1899 16:50:32". While the time is correct, the date most definitely is not.

How can I fix this?

1. Re: Error in Date Interpretation

Mark Fraser Oct 9, 2015 1:43 AM (in response to Andreas Jagdhuber)

Hi Andreas

Which database are you connecting to? SQL, Oracle, Access...
Does it happen with all date fields or only one?

Cheers
Mark
Like Show 0 Likes(0)

Actions
2. Re: Error in Date Interpretation

Mark Fraser Oct 9, 2015 1:44 AM (in response to Mark Fraser)

Is it a Microsoft data source? Resolving Date Discrepancies Between Jet and Tableau Data Engines | Tableau Software
Like Show 0 Likes(0)

Actions
3. Re: Error in Date Interpretation

Tableau kumar Oct 9, 2015 1:46 AM (in response to Andreas Jagdhuber)

Hi,

Once connected to the DB, right click on that Date field then pick the
Format.

Best Regards
Tableau Kumar

On Fri, Oct 9, 2015 at 2:10 PM, Andreas Jagdhuber <
Like Show 1 Likes(1)

Actions
4. Re: Error in Date Interpretation

Andreas Jagdhuber Oct 9, 2015 2:02 AM (in response to Mark Fraser)

It's a MySQL. And changing the format doesn't solve the problem. Have been trying that already. If I select "Exact Date", it changes to todays date.

And it only happens to that particular field. Another date field, which is formated dd.mm.yyyy in the database, works fine.
Like Show 0 Likes(0)

Actions
5. Re: Error in Date Interpretation

Mark Fraser Oct 9, 2015 2:16 AM (in response to Andreas Jagdhuber)

Thanks Andreas - no formatting wouldn't help.

Unfortunately I don't have MySQL nor experience with it, so I'm not sure how much I can help
Things I would suggest -

Are you connecting with ODBC or native MySQL connection? I would use native
Drivers - Make sure they are up to date and common across database/Tableau Drivers & Activation | Tableau Software
Is it a live connection or extract? Extract may solve the problem
Do you have other sources from the same database which are OK? Is it a single source/ single field?

Which version of Tableau are you using? < later is better, I couldn't find it on Known Issues | Tableau Software but you never know!

The reason for the questions is to understand and help others to help you

Cheers
Mark
Like Show 0 Likes(0)

Actions
6. Re: Error in Date Interpretation

Andreas Jagdhuber Oct 9, 2015 12:21 PM (in response to Mark Fraser)

Sorry for the late reply. It was a live connection but I switched to an excel sheet. Didn't change things though. And it only concerns this one date-time field. Another date field (dd.mm.yyyy) is displayed normally. I'm using 9.1 by the way.

I also checked the drivers for MySQL - I'm up to date there as well. Can I fix this some other way?
Like Show 0 Likes(0)

Actions
7. Re: Error in Date Interpretation

Mark Fraser Oct 12, 2015 2:51 AM (in response to Andreas Jagdhuber)

Its so very strange...

The date... its been bothering me... I helped on this thread newbie: how to do sql equivalent of group by and partition that's not important, but the date is the same 1899. Then I went to my source, I had just the times in Excel, no dates.
vs
Tableau defaulted the date to 30/12/1899 when no date element is present.
I can't help thinking for whatever reason that's what is happening in your situation... you can quickly replicate it yourself with Excel

This is a guess - in Microsoft Access the first row of the data set defines the type of the field i.e whether its a date, number etc. I don't know if Tableau does the same but - Could the problem be the first item in that particular column?
The theory being its affecting the data type of the whole column and causing the issue.

Failing that - you could try extracting the data (rather than use Excel)...

If none of that fixes it, I would suggest raising a call with technical support so they can troubleshoot that workbook
You can reference this thread.

Hope something works!!

Cheers
Mark
Like Show 0 Likes(0)

Actions
8. Re: Error in Date Interpretation

Andreas Jagdhuber Oct 12, 2015 3:33 AM (in response to Mark Fraser)

Hi Mark,

that's an interesting thought. I checked the source and the first data set from that particular column is "null". Meaning: It's empty because the datetime didn't exist at that point.

We also checked the database and found, that in MySQL, this particular column is formatted as time, not datetime.

I'm not sure where all this leaves me, but now I'll try to parse a datetime out of the date field that works and the (wrongly interpreted) datetime field.
Like Show 0 Likes(0)

Actions
9. Re: Error in Date Interpretation

Mark Fraser Oct 12, 2015 3:38 AM (in response to Andreas Jagdhuber)

Great news, I think we have it

But as you say - its what to do now?!
If its a single record - could you enter a dummy value (and transform it back to null Tableau side) or simply exclude it?

Cheers
Mark
Like Show 0 Likes(0)

Actions
10. Re: Error in Date Interpretation

Andreas Jagdhuber Oct 12, 2015 5:03 AM (in response to Mark Fraser)

Unfortunately, I can't access the database itself or change any values it contains. My dateparsing isn't working out either. I'm a little clueless about what to do.
Like Show 0 Likes(0)

Actions
11. Re: Error in Date Interpretation

kettan Oct 12, 2015 5:25 AM (in response to Andreas Jagdhuber)

Assuming that the data type was converted to datetime: What about leaving the date part as it is and ensure that it is always formatted as time only as already suggested by Tableau kumar? What is the data type in Tableau? Datetime? Text?
Like Show 1 Likes(1)

Actions
12. Re: Error in Date Interpretation

Mark Fraser Oct 12, 2015 5:22 AM (in response to Andreas Jagdhuber)

That's a shame - in that case we can only fix the output...

One way would be to remake the date in a calculated field
IF YEAR([Date])=1899 THEN NULL ELSE [Date] END
We can then write another calculated field to exclude the null values
You should now (hopefully) have a 'clean' set of dates which you can format, pars, whatever you need to do...
Would that work for you?
Note - All the above is just a workaround - ideally it would be fixed at source.

Cheers
Mark
Like Show 0 Likes(0)

Actions
13. Re: Error in Date Interpretation

Mark Fraser Oct 12, 2015 5:26 AM (in response to kettan)

Hi Kettan

After a long thread - I think we have discovered the issue relates to the way Tableau interprets empty date strings.
The problem relates to Tableau defaulting a blank/empty date to 30/12/1899... I think (like Access) Tableau uses the first row to determine data types < and that were the issue is.
The OP can't amend the source - so my plan is to deal with the consequences instead. (see above)

Thank you - up voted as well - I find it odd there are various date/time functions apart from time

Hope you're well

Cheers
Mark
Like Show 1 Likes(1)

Actions
14. Re: Error in Date Interpretation

kettan Oct 12, 2015 5:34 AM (in response to Andreas Jagdhuber)

I just looked at an old question about this – mySQL Time – and see that its time is actually duration, that is, it can contain more than 24 hours. You might like to go through various previous answers about the issue. Here is a search link: http://community.tableau.com/search?q=mysql+time

Can you convert the time (duration) field to a number? If yes, what is the greatest number? And what is the time's starting point, if any?

In this context, you may like to up-vote Format number as duration.
Like Show 0 Likes(0)

Actions