1 Reply Latest reply on Feb 25, 2020 11:35 AM by Sinisa Mikor

    "Ambiguous column name 'DATEDIFF'"

    Jerome Walker
      Share This:



      I am having this error when trying to calculate the difference between two dates. It should calculate the number of days, of course. It seems, or feels, like this Calculated Field NEVER EVER works for me. Is it my imagination only? That's not the question... But it is so frustrating trying to make things work in Smart Reporting. Why can't it just do what you want it to do? Why do so many things have to be like pulling teeth or bloodletting? Once again, not the question.


      Difference between today and a date. I want a column that gives the number of items that are less than one year, e.g. 365 days, for each organization. I get this error "Ambiguous column name 'DATEDIFF'".


      So here's another question, not the actual question, of course. Why can't these error messages be better worded? Why can't they actually make sense so that you will have a better idea to fix the error so you can get the information you need??? (And I always wonder when I encounter these things... is it because ARJDBC error messages that are like this, or are the people at YellowFin just making error messages weird and cryptic, or is it the people at BMC who are making up secret, encrypted error messages just for fun. Whenever something works crazy or is stubborn and clunky in Smart Reporting I'm always wondering if it's Yellowfin or BMC or someone or something else that is making life so... challenging. Or am I just uneducated, or even downright stupid.)


      Enough complaining... The real question is how to do this. And... where I work, we do not create fields using Freehand SQL. We are advised not to by the group that I work with for my employer. Sorry, that's just how it is.


      Any help would be greatly appreciated.


      Quick question is how would you do Start Date and End Date in that DATEDIFF Days Predefined Formula to return positive numbers, make today's date the Start Date or the End Date (and the date that takes place in the future as the other date, of course)? After that I used it in another Calculated Field (Simple Formula Type) to count the number of items that are less than 365 days away from having their warranties expire. On an additional note to help, I got the error when I used a Field called Expiration Date in the Category Asset Contract - Warranty. That's when I got the error. At the end of the day yesterday I discovered the Field called Warranty End Date in the Category Time. I'll try this and hope it helps. As an aside I'm curious what that first field is supposed to represent... what information or data it is supposed to return.


      I hope this helps answer my question. And once again just as an aside, I wish I new the answers to those other questions and I wish the error messages would actually tell you what the error is IN REALITY... in more sensical language that would actually make sense.


      Thank you all ever so very much in advance!!!

        • 1. Re: "Ambiguous column name 'DATEDIFF'"
          Sinisa Mikor

          Hello Jerome,


          there should be a few ways to achieve your goal and here are some:


          1. Error you mention usually pops up until form date attribute is coming from is tagged as mandatory in view report uses, so you could edit its view, mark appropriate form as mandatory and probably be able to use DATEDIFF
          2. There is a hidden function DATEADD('year', <number of years>, <date attribute>), but you must use Freehand SQL Formula Type to enter that.
          3. Try subtracting values directly (e.g. CurrentDate() - <date attribute>) to calculate difference between timestamps; it's in seconds, so a year would roughly be equal to 365 * 24 * 60 * 60 of those