7 Replies Latest reply on Aug 31, 2016 12:10 AM by Steve Taylor

    Help with converting string to date using calculated fields

    Phillip Gaertner

      Hi all,

       

      I'm having an issue with converting a string value to a date field so i can use it more effectively in my reports.

       

      I have undertaken a search and found this Fix Date Fields Interpreted as Strings | Tableau Software  article that appears to address my issue BUT when I follow the steps the calculated field moves the days and months around. I have attached an image as I can't package a workbook due to data sensitivity.

       

      The data is simple dd/mm/yyyy all I want to be able to do is convert it to a date field.

       

      I tried the dateparse but that wont work for me as I need to access live data and I'm using MS Server and just changing the data type also switches the days and months around.

       

      Any ideas what I'm doing wrong? I'm sure it's simple I just can't see it.

       

      Thanks in advance.

      Phil

      (noob to Tableau)

        • 1. Re: Help with converting string to date using calculated fields
          Steve Taylor

          Hello Phil,

           

          This might have something to do with how your datasource is set up to display dates.

           

          If you right click on your datasource, select the Date Properties menu item a window pops up in which you can specify week start, fiscal year start and date format. It'll probably be set to automatic but you can change it to display dd/mm/yyyy like so:

           

           

          If you use this method I think using DATEPARSE() function will run quicker than the calculation in the image you attached.

           

          Hope that helps, let me know if it doesn't work for you

           

          Steve

          • 2. Re: Help with converting string to date using calculated fields
            Phillip Gaertner

            Hi Steve,

            I have followed the steps mentioned above but it has made no difference to field; it is still displaying as it did in my first image.

            I haven't used the DATEPARSE() as speed isn't an issue at the moment only getting the data correct is.

            Any other ideas on what I'm doing wrong.

            Cheers

            Phil

            • 3. Re: Help with converting string to date using calculated fields
              Steve Taylor

              You can also edit the date format by right clicking on the date field and selecting default properties > date properties and if you choose the option of 14/03/2001 that can override the date properties setting at datasource level.

               

              Another way would be to right click on the date in a view and select Format and change the Dates section to 14/03/2001

               

              I apologise if you already knew about those options, I'm just trying to figure out what might be causing the date to appear wrong because when I've tried to replicate the error (using some dummy data in excel) I can't get the same error as you do.

               

              If neither of those things work, are you able to share your workbook (save as a .twbx) on this thread? That'll make it much easier to find a solution (obviously remove anything customer sensitive, you could just take the date fields and paste into excel then connect to that and see if you can replicate the error)

               

              Thanks

               

              Steve

              • 4. Re: Help with converting string to date using calculated fields
                Phillip Gaertner

                Well, I have tried everything suggested and still no joy.

                 

                I have tried to change the format at the DS and in the view with no luck.

                 

                But the plot thickens.

                 

                I exported a small subset of the data as a CSV than added it as a new data source and both the fields that are  string when connected to the live data are automatically detected as Date fields, exactly as I need them. So I'm lost as to what the issue is.

                 

                Unfortunately exporting the data is not a suitable solution as I need to be able to refresh these reports from the source weekly (preferable from the Tableau server) along with about 10 other large dashboards. The prod data source has about 3 million rows so converting to CSV just doesn't work.

                 

                Are there any other options e.g convert the data in the SQL query as changing the DB is too big of task as regression testing would be cumbersome.

                 

                Thanks

                Phil

                • 5. Re: Help with converting string to date using calculated fields
                  Steve Taylor

                  If your data connection is Custom SQL then I'd definitely try to convert the date text field to a date field in the query, something like SELECT convert(datetime, <Your Date String>, 103) might work, depending on your database (obviously Oracle would be different).

                   

                  You could even try, in the datasource window, changing the data type of your date field from string to date by clicking on the "Abc" menu highlighted here:

                   

                  Really hope we can work out a solution, sounds like a really frustrating issue!

                  1 of 1 people found this helpful
                  • 6. Re: Help with converting string to date using calculated fields
                    Phillip Gaertner

                    Thanks Steve,

                    I modified the query this morning and all seems to be working.

                    Adding the "convert" worked but when I added a WHERE on that field I received errors due to dodgy data but one of the more technical guys here fixed the issue with

                     

                    WHERE ISDATE([vwComplianceAssessment].[CA_DATE]) = 1 AND CONVERT(datetime, [vwComplianceAssessment].[CA_DATE]) >= '2016-01-01'

                     

                    Thanks again for the input.

                     

                    Cheers

                    Phil

                    • 7. Re: Help with converting string to date using calculated fields
                      Steve Taylor

                      Brilliant, glad you found a solution.