2 Replies Latest reply on Apr 12, 2012 11:53 PM by harry.schroen

    how can I import SQL date fields in a suitable format

      When I mark a date field as date, numerous NULL values appear.

      The date field comes from a SQL table.

      Have I missed a setting in Tableau or should I custom the SQL query?

        • 1. Re: how can I import SQL date fields in a suitable format
          Russell Christopher

          Hi Harry -


          I'm going to make some assumptions here:


          1. 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
          2. 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.

          1 of 1 people found this helpful
          • 2. Re: how can I import SQL date fields in a suitable format

            Hello Russel


            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).