SQLTeam.com | Weblogs | Forums

TURNC date in Oracle to SQL

No modulo for the year calc, of course not. But the monthly calc would be different.

Oracle apparently counts days, whereas SQL Server counts month boundaries. In SQL Server, you'd have to do a days diff or just check the days and subtract 1 month if the to day is less. You'd likely be better off just writing your own function to do that.

Please keep in mind that this is a SQL Server forum. I don't make any claim to knowing any details of how Oracle functions work and/or the results they return vs. SQL.

Sorry, I was referring to the O/P, because I had not spotted the follow-up which included [MonthsSvc] :frowning: ... Old Age ...

Just pick a couple of functions. If it returns a date type, leave it, and call it a day.

See who bitches. Ask them what the correct answer is.

Thanks Scott, Yeah I think my problem is with the first part of equation, the mod is working fine. I have not done a function before, need to look it up.

Pasi

Another thing, I think in equation I have ( first part)
first part it starts from month '0' --> TRUNC(TRUNC(MONTHS_BETWEEN (,13-may-2009, HIRE_DT), 0) /12) As YearsSvc, ( does SQL do the same?)

Here is the whole thing in Oracle:

--TRUNC(TRUNC(MONTHS_BETWEEN ({?ReportsEffectiveDate}, HIRE_DT), 0) /12) As YearsSvc,

--(TRUNC(MONTHS_BETWEEN({?ReportsEffectiveDate}, A.HIRE_DT), 0) ) - (TRUNC(TRUNC (MONTHS_BETWEEN({?ReportsEffectiveDate}, A.HIRE_DT), 0) /12)) * 12 As MonthsSvc,

so please explain
YearsSvc as it relates to MonthsSvc

  1. is MonthsSvc the number of months in YearsSvc
  2. The remainder number of months?
  3. or?

Because you want to ignore partial months, which SQL functions do not do, do this in SQL Server:

(DATEDIFF(month, HIRE_DT, ReportsEffectiveDate) - 
    CASE WHEN DAY(HIRE_DT) > DAY(ReportsEffectiveDate) THEN 1 ELSE 0 END) / 12 AS YearsSvc,
(DATEDIFF(month, HIRE_DT, ReportsEffectiveDate) - 
    CASE WHEN DAY(HIRE_DT) > DAY(ReportsEffectiveDate) THEN 1 ELSE 0 END) % 12 AS MonthsSvc

Thanks! that works great!..

this is how I did it but not sure if correct but it works? for month of SVC: (added '-1' to calculation)
(DATEDIFF(month, hire_dt,'2009-5-13')-1) - (DATEDIFF(month, hire_dt,'2009-5-13') /12) *12 As MonthsSvc,

No. I gave you the exact calc you needed. If you want a "simpler" way, you can use it, but it will not be accurate all the time.

That's all I can do. I can't spend any more time on this. Good luck with it.

Not a prob. I am using yours is more accurate.
Pasi.