6 Replies Latest reply on Mar 1, 2016 7:42 AM by Joe Oppelt

    How to convert a numeric field to a usable date field?

    Phillip Davies

      I have a field that is in a numeric format from a SQL database (ex: 20,150,101).  It is entered as the year,month,day.  I would like to convert this into a date field that I can then filter against.  I have been able to successfully do this if I do an Excel extract from the database and connect to the worksheet using Tableau.  I can also successfully do this if connecting to the database directly using Crystal Reports.  I am running into an error each time with Tableau when connecting straight to the database.

        • 1. Re: How to convert a numeric field to a usable date field?
          Cynthia King

          Do you have the text of the error message?  

           

          There are a couple of things you can do.

           

          Did you try dragging the field up from measures into dimensions, then right clicking on the field and selecting "Change data type", then you select "Date"?

           

          It would be helpful to know the error you are getting and what you have tried.

          1 of 1 people found this helpful
          • 2. Re: How to convert a numeric field to a usable date field?
            Joe Oppelt

            It would be helpful to have a sample workbook.


            For starters, the problem statement says, "... a field that is in a numeric format from a SQL database (ex: 20,150,101).  It is entered as the year,month,day. ..."

             

            That doesn't look like any year-month-day I recognize.  So I would want to see this first hand to understand it better.

             

            Edit to add:  Oh, wait...  I was seeing that as three separate numbers:  20.   150.   101.  Now I see that it's 2015.  01.   01.

             

            So what you can do is this.  Year = INT([numeric value]/1000)

             

            Month = INT( ([numeric value]-(year*1000))/100 )

             

            Day = INT(  ([numeric value]-(year*1000)-(Month*100)) )

             

            Essentially, break it into individual components.  Then plug those into DATE to make a DATE field..

             

            Another way is to convert the number to STR.  Grab first 4 chars of the string for the year,  position 5-6 for the month, and position 7-8 for the day.  And then plug those values into the DATE function.

            1 of 1 people found this helpful
            • 3. Re: How to convert a numeric field to a usable date field?
              pooja.gandhi

              If your commas are indeed off in your question and you actually mean (2015,01,01) you could use the MAKEDATE() function:

               

              1 of 1 people found this helpful
              • 4. Re: How to convert a numeric field to a usable date field?
                Phillip Davies

                I tried changing it to a dimension from a measure, changing the type to a date, and then filtered on the field....I get this error:

                 

                [IBM][CLI Driver] CLI0118E  Invalid SQL syntax. SQLSTATE=37000

                Unable to properly calculate the domain for the field 'Pst Dat'. Displayed data may be incorrect.

                • 5. Re: How to convert a numeric field to a usable date field?
                  Cynthia King

                  I googled your error.  It looks like the error  may be on the database side.

                   

                  Tableau wraps everything into SQL before is sends it to the database.   And sometime the database doesn't like it and returns the error.  Tableau doesn't try to fix it.  It doesn't happen often, but sometimes.  On my database you can't embed SQL with an "order by" in it.

                   

                  I am not an expert but it looks to me like the query that Tableau is sending to your database, DB2?  Is adding a line charater to the SQL string which causing the database to error.  You may have to have your support folk look at installing the software to fix it.  I recommend that you put in a case ticket with Tableau as well.  Below it is  a link to the information.  Anyway that is what I would do.

                   

                  IBM IC77957: CLI0118E INVALID SQL SYNTAX WHEN PARAMETER LISTING CONTAINS NEW LINE CHARACTER - United States

                  • 6. Re: How to convert a numeric field to a usable date field?
                    Joe Oppelt

                    You're going to have to parse the field in a calc, and then use the calc as a date in place of where you would want to use your data field.

                     

                    I made some suggestions yesterday about how to parse it.  I had forgotten about the MAKEDATE function that Pooja pointed to.  With a YEAR, MONTH and DAY breakdown, you can do

                     

                    MAKEDATE([Year Part],[Month Part],[Day Part])

                     

                    And, in fact, you can skip the interim calcs for the individual parts and just put the syntax for each interim calc right into the appropriate slots of the MAKEDATE function.  When you are done, you will have a calc that is a date format, and it will give you all the date functionality available to you in Tableau.