1 of 1 people found this helpful
Hi Harry -
I'm going to make some assumptions here:
- The data is not actually stored in SQL Server in a true date/time field - if it was, there would be no need for you to change the data type to begin with
- Some of the "dates" (maybe stored as strings in SQL) don't follow the format that we expect them to be in when we attempt to MAKE them dates.
If 1 & 2 are both true, I suspect that the date conversion is simply failing on rows that aren't formatted as "proper" dates: mm/dd/yyyy or somesuch.
It would be useful if you could actually identify a row or two in SQL that doesn't convert correctly and post those values here.
I'm betting your data is just a bit "dirty" and in some cases needs to be cleaned up - either your "SQL guy" can do that for you, or you might be able to do it yourself by writing an expression that pulls apart a "mal-formed date", and puts it back together in a more standard way.
For example, this expression is "fixing" a "string date" stored in this format yyyy-mm-dd:
Date(MID([BASE_DATE_OLD], 6,2) +
"/" + MID([BASE_DATE_OLD], 9,2) +
"/" + LEFT([BASE_DATE_OLD],4))
It takes characters 6-8 (mm), adds a "/", plugs in characters 9-11 (dd), plugs in another "/", and then takes characters 1-4 (yyyy)...it finally turns the whole thing into a date.
Give us a little bit more information to go on, and I'm sure we can point you in the right direction.
The dates from the SQL table are strings indeed (eg. 2012-12-24).
I can't find the false dates, so far.
With Tableau, the number of NULL values increases when I put more dimensions on the shelf (see attachment).
StrangeDateNullVal.docx 200.9 KB