diff_days function has parameters backwards from expected
I used the diff_days function today for the first time and the order of the two inputs is the opposite of what I'd expect. It works properly according to the documentation, which states:
Subtracts the second date from the first date and returns the result in number of days, rounded down if not exact.
This means that you run the function like this:
diff_days(01/31/2017, 01/01/2017) = 30
However, this is the opposite of Excel and T-SQL - in those systems, the result is the number of days between the first and second date, so that the example above would be negative (since the first date is later). This caused some confusion and results in my formulas not being intuitive.
I'm not sure how you correct it without breaking everybody's formulas on existing sheets when they use this function, but if you're planning to introduce something like a "diff_period" function in the future (that takes three parameters - period, date1, and date2), it would be a good chance to line up the layout with other major systems.
Thank you for listening!
Ryan McCauley ,
You are correct in that T-SQL's Datediff function takes in the start date before the end date.
But many other systems like Excel and and MYSQL take in the end date first followed by the start date
In Oracle or in other programming languages like Java we simply do "Date2" - "Date1".
We were internally conflicted with respect to the order of the parameters as there is no one single approach that is standard in the database world and took the second approach.Reply
Thanks for the detail - I didn't realize that other systems did it in reverse as I've only ever used it in Excel and T-SQL. I've never used the DAYS function, but used DATEDIF instead (which takes the dates in first, second order).
I appreciate that picking a side isn't always easy and I'll keep the order in mind when developing formulas moving forward!Reply