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

vtrinity7Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
>>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)
/
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
vtrinity7Author Commented:
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).
0
 
vtrinity7Author Commented:
Formula worked after a little tweaking
0
 
vtrinity7Author Commented:
solution worked after tweaking
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.