Convert SQL Server fomulas to PL/SQL for Oracle 10G

Good Morning. I have 3 date formulas from SQL Server that need to be converted to Oracle 10G syntax. I've found that Oracle syntax does not have the Dateadd function as in SQL Server, therefore I am a bit lost on how to create the formulas.

The first formula calculates the start date of the rolling year based on the current date. For instance today's date is 03/28/2011, therefore the start date for the rolling year range is 03/01/2010. The formula is:
DATEADD(yy, -1, CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) - (DAY(GETDATE()) - 1) AS DATETIME)) AS StartDate

The second formula calculates the end date of the rolling year based on the current date. For instance today's date is 03/28/2011, therefore the end date for the rolling year range is 02/28/2011. The formula is:
DATEADD(dd, -1, CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) - (DAY(GETDATE()) - 1) AS DATETIME)) AS EndDate

The third and last formula is for calculating the number of hours worked during the week based on a hiredate that is in a table called rolling. The formula also utilizes the begin and end date of the rolling period from the previously mentioned formulas. The formula is:

wkh = (((DATEDIFF(dd, roll.hiredate, roll.enddate) + 1)-(DATEDIFF(wk, roll.hiredate,roll.enddate) * 2) -(CASE WHEN DATENAME(dw, roll.hiredate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw,roll.enddate) = 'Saturday' THEN 1 ELSE 0 END))*8)

Any assistance will be greatly appreciated.

Thank you in Advance.

Vasheti

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 80

slightwv (䄆 Netminder) Commented: 2011-03-29

First one should be something like:
add_months(trunc(sysdate,'MM'),-12)

Second:
add_months(sysdate,-13)

Can you explain a little more about the third and fourth?

subtracting dates in Oracle returns the number of days but I don't understand the logic you provided.

vtrinity7Author Commented: 2011-03-29

Thank you for the quick response. The first two worked perfectly. I only had to change the 2nd formula to add_months(sysdate,-1) to yield the correct end date of 02/28/2011.

Actually, there are only 3 formulas, the third one is just very long. The third formula does calculate the number of work days (which excludes Saturday and Sunday) between a person's hire date and the end date of the rolling year, which is 02/28/2011. Once I get the number of days, I multiply by 8 (total number of work hours in a day). I did not explain it correctly, previously. Basically, if my hire date is on 12/31/2010, I want to calculate the number of work days between the hire date(12/31/2010) and the end of the rolling year date (02/28/2011), excluding the weekend days (Saturday and Sunday). This will return the number of days. Once I retrieve the number of days (42 days), I multiply by 8 to get number of hours (336 hours).

>>correct end date of 02/28/2011.

oops. Sorry I read 2010 not 2011.

>>Once I retrieve the number of days (42 days)

Check out the following

select ((count(1)+1)*8)/336 from
(
      select to_char(to_date('12/31/2010')+level,'D') day_of_week
      FROM dual
      CONNECT BY LEVEL <= to_date('02/28/2011') - to_date('12/31/2010')
)
where day_of_week not in (1,7)
/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

vtrinity7Author Commented: 2011-04-21

Formula worked after a little tweaking

solution worked after tweaking

Convert SQL Server fomulas to PL/SQL for Oracle 10G

Who is Participating?