1 Reply Latest reply on Nov 14, 2012 2:06 PM by Mark Holtz

    Custom SQL and Date Conversion

    Glenn H

      Background Info:

       

      I am currently running extracts from a Pervasive SQL database.  All the fields come over as strings and the date field comes over as YYYYMMDD.  I am able concatenate the string to make a normal looking date field. 

       

      I am running into an issue when I try to convert it to a date type field.  The conversion itself works fine until I go to refresh the extract.  At that point, all of the calculated fields are all equal to NULL.  There is no information in these fields and I am unable to edit the field like a calculated field should be editable.

       

      I can revert back to a working version of the extract if I delete the date converted field and re-run the extract.

       

      Question:

       

      I had a few thoughts on potential solutions and was hoping I could get some assistance following through on them as I could not find anything in the help files or forums related to it.

       

      First, as I have not done SQL in a long time and I am unsure of what capabilities the custom SQL has, is there a way to convert the field using a custom sql statement and changing it before it even comes through to Tableau?  Would I run into an issue with the YYYYMMDD format if I tried to convert it into a date directly?

       

      Second option I was considering is to turn the string into an integer and then concatenate it that way.  The problem I run into is extracting the MM and DD part easily.  In addition, the DATE type requires the /'s so I would have to reconvert it into a string anyway so the viability of this potential solution is low.

       

      Any other thoughts are greatly appreciated.  Thank you.