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

Datediff giving odd results.

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.

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.

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.