SQLTeam.com | Weblogs | Forums

TURNC date in Oracle to SQL


#21

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


#22

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.


#23

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


#24

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.


#25

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


#26

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,


#27

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?

#28

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

#29

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,


#30

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.


#31

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