In case helpful for others here's the Oracle DOCs for those functions. I've read them and not sure I understand them (in particular all the nuance and edge-conditions that seem to be implied / inferred)

```
MONTHS_BETWEEN (ReportsEffectiveDate, HIRE_DT)
```

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. *Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2*.

I don't understand the point in italics, and whether or not this applies in this case (and, for two random/unknown dates, whether you would know if it was GOING to apply ... or not ...)

But seems like this is likely to return INT ?? or a fractional part if the dates are not same-day. MS SQL won't do that, so there is a very real chance that for **some** values of Date1 and Date2 the calculation in MS SQL will need to be sophisticated to accommodate that.

So I first looked at

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day. Please refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.

but I now realise that, presumably, I should be looking at TRUNC(integer). There is no cross reference in the DOCs so I don't know if my search did not find other variances explicitly for INT or similar ... so hopefully this is the only and correct option:

The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you omit n2, then the function returns the same datatype as the numeric datatype of the argument. If you include n2, then the function returns NUMBER.

So: `MONTHS_BETWEEN`

calculates the months between two dates, then rounds that normally-INT possibly-FLOAT value to 0 d.p. (surely unnecessary if it is already an INT?) and then divide that by 12 and TRUNC that and because default parameter used that will, again, round to 0 d.p. - but surely one integer divided by another integer is going to give an INT result, rather than a FLOAT? Either way (and assuming that IS the intended outcome) then that's going to be the default outcome in MS SQL.

In which case won't this be enough?

```
DATEDIFF(month, ReportsEffectiveDate, HIRE_DT) / 12
```

But I have no idea if HIRE_DT is a date later in the month than ReportsEffectiveDate what the upshot will be in that edge condition. In particular for things such as 28 Feb in a leap year compared to 29 March (which are not the end-of-month days special-case described in the DOCs, so maybe 1 month more than MS SQL would calculate as the DIFF ?

but more worryingly demonstrates that "convert Oracle to MS SQL" should perhaps actually be "rewrite Oracle in MS SQL" and that requires understanding what the original intent of each statement is, as much as translating the code, because in this instance it looks, to me, like the person that wrote the Oracle code put in stuff that is redundant / not required and if converted, verbatim (e.g. if a CONVERT to VARCHAR and back to INT was used) that would be a performance bottleneck at best and at worse a rats-nest of bugs ...