SQLTeam.com | Weblogs | Forums

TURNC date in Oracle to SQL


HI, I have below code to Turncate from oracle to SQL is this correct? does not look right conversion??

-TRUNC(TRUNC(MONTHS_BETWEEN (ReportsEffectiveDate, HIRE_DT), 0) /12) As YearsSvc, --Oracle

CONVERT(DATEDIFF(month, ReportsEffectiveDate, HIRE_DT), 0) /12) As YearsSvc --SQL


what is the final result you would like to see?


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 ...


Something like this:



CONVERT(DATEDIFF(month, ReportsEffectiveDate, HIRE_DT), 0) /12) As YearsSvc --SQL

so the field ReportsEffectiveDate is like 5/13/2009 if that helps. it a parameter set by user. and sometime is static just like 5/13/2009


so that value of 3 is what? a month, a date or a year?


its a year number of service (year).


why not just do

select DATEDIFF(year, '5/13/2009', '01/01/2018')


Thanks that might work, the year of service is between 5/13/2009 and HIRE_DT, I set it to :slight_smile:
DATEDIFF(year, '5/13/2009', HIRE_DT) As YearsSvc but for some reason it doesn't like the HIRE_DT field?



well you tell me where you are getting that column from?


HIRE_DT is from a table called Empl_hist.


expound on "doesn't like it" I have never seen a SQL error "Doesn't like it" Is this an error in SQL 2020? :zipper_mouth_face:


No its SQl server 2012. what I mean by doesn't like it, I get a red underline below HIRE_DT.


what database is this Empl_hist part of and have you selected that database ie

USE Pasi

Select DATEDIFF(year, '5/13/2009', HIRE_DT)
from Empl_hist


Select DATEDIFF(year, '5/13/2009', HIRE_DT)
from Pasi.dbo.Empl_hist


Yes the dB is correct I think is the alias name I need to fix. I think your formula should work.


No, you cannot use DATEDIFF(YEAR, ...). You need to months, as in the original calc:

DATEDIFF(month, HIRE_DT, ReportsEffectiveDate) / 12 As YearsSvc


Thanks Scott, you right I was using YEAR and wasn't getting the right result. what am I doing wrong here?
I am getting right years of service but not month of service? I am getting month

my code:

DATEDIFF(month, hire_dt,'2009-5-13') /12 As YearsSvc,
(DATEDIFF(month, hire_dt,'2009-5-13') - (DATEDIFF(year, '2009-5-13', HIRE_DT))/12) *12 As MonthsSvc

I am suppose to get this for month of service


but getting this:


DATEDIFF(month, hire_dt,'2009-05-13') % 12 As MonthsSvc


Thanks I am off by one, I am getting 4 for month of svc but should be 3.


Doesn't look like the original code is doing modulo ...