3 Replies Latest reply on Mar 11, 2016 2:08 AM by Ashish Chaudhari

    Dates in Tableau

    Julia Hennelly

      Have checked forum articles and have no clue why my date functions are not working as I would expect.  Thinking I may need to do the date conversion in SQL.  I have an integer field that contains the order date.  I have tried DATEPARSE and DATE as shown below:

       

      DATEPARSE("YYYYmmdd", str([Order Received Date Key])).  This gives VERY strange results such as converting 20150325 into 1/25/2015 with minutes of the time portion set to the month???

       

      I also have tried the DATE function as shown below and this returns null.

       

      date('"' + MID(str([Order Received Date Key]),5,2)+'/'+

      RIGHT(str([Order Received Date Key]),2) +

      left(str([Order Received Date Key]),4) + '"'

      )

       

      without the date function it returns "mm/dd/yyyy" so I would expect the date function to work.

       

      I am attaching a workbook and would appreciate best practice in dealing with converting to dates - should this be done in SQL or can Tableau handle it.  Why am I getting the unexpected (at least from me) results.

       

      Thanks in advance,

      Julia

        • 1. Re: Dates in Tableau
          Corey Jones

          Hi Julia,

           

          I believe the DATEPARSE function will work if you change the months in the format part of the equation to a capital "M".  I am not sure why this is giving you an issue-- maybe someone else who understands date formats better can chime in? 

           

          I noticed on this page about the function all the times months was used it was a capital M. 

          Understanding the DATEPARSE Function | Tableau Software

           

          I downloaded your workbook and it works for me. Please let me know if you have any questions.

          My calculated field is: DATEPARSE("yyyyMMdd", str([Order Received Date Key])).

           

          Thanks,

          Corey

          • 2. Re: Dates in Tableau
            Julia Hennelly

            That makes sense now I look at it as the small m was being interpreted as minutes hence the month showing in the minutes.  Weird but at least logical to some extent.

             

            Julia Hennelly

            Manager, Wholesale Analytics

             

            Tel: 708.884.2029 | Fax: 630.850.1874

            Follett Higher Education Group

             

            Three Westbrook Corporate Center, Suite 200

            Westchester, Illinois 60154

            jhennelly@follett.com<mailto:jhennelly@follett.com> | www.follett.com<http://www.follett.com>

            • 3. Re: Dates in Tableau
              Ashish Chaudhari

              Hi Julia,

               

              Please find the formula that I have used to solve your problem.

               

              MAKEDATE(INT(LEFT([Order Received Date Key],4)),

              INT(MID([Order Received Date Key],5,2)),

              INT(RIGHT([Order Received Date Key],2)))

               

              Make date takes the input as the in year, month, date format.

               

              Please refer to the below screenshot.

               

              Makedate.png

              I hope this would help you. Let me know if you still have any questions about this.

               

              If you liked it then please like the comment and click on That helped.

               

              Ashish Chaudhari.

              3 of 3 people found this helpful