2 Replies Latest reply on Jun 4, 2016 9:25 AM by Daniel Schwan

    Datediff giving odd results.

    Daniel Schwan

      I have a MS SQL data source with two date fields in each row: [Received UTC] and [Delivered UTC].  The domain of each includes nulls. I have a simple date diff to calculate the number of minutes between the received and delivered times. The below is copies straight from Tableau desktop. As I understand it, the formula should result in an integer between 0 and 1,051,200. But the listed Domain far exceeds that.

       

      A couple of notes:

      • as a result of some incorrectly entered dates, I was getting datediff overflow errors in the datasource so I include a check to make sure the dates are within 2 years of each other.
      • if either of the dates are NULL then they should fail the first if check and result in NULL for the calculation

       

      Can someone explain where I'm going wrong? Thanks.

       

      Formula:

      IF [Received UTC] <= [Delivered UTC] THEN

          IF DATEDIFF('year', [Received UTC], [Delivered UTC]) >= 2 THEN

              365*24*60*2

          ELSE DATEDIFF('minute', [Received UTC], [Delivered UTC])

          END

      ELSE NULL

      END

      Domain:

      0 to 14,914,854

        • 1. Re: Datediff giving odd results.
          Adam.Cogswell

          Datediff works a little differently than you expect. It's only calculated based on the datepart you give it. So for example, if you did DATEDIFF('year',#2014-01-01#,#2016-12-31#), it would see 2016-2014 = 2, even though those dates are way more than 2 years apart. So in your case, you'd probably want to do your 'within two years' check with something like DATEDIFF('day',[Received UTC], [Delivered UTC]) <= 730.

          • 2. Re: Datediff giving odd results.
            Daniel Schwan

            Thanks, you're right. I'm looking to set an upper limit so DATEDIFF('year',#2014-01-01#,#2016-12-31#)=2 is not a problem, but DATEDIFF('year',#2015-12-31#,#2016-01-01#)=1 is a problem for my check. I switched to weeks.

             

             

            That improves my code, but doesn't answer the question about the domain exceeding what should be the result of the calculation. Turns out I found a Tableau bug that was giving crazy results for some records. Support hasn't yet figured out what set of conditions create the crazy results.