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
Any assistance will be greatly appreciated.
Thank you in Advance.
Vasheti
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
Any assistance will be greatly appreciated.
Thank you in Advance.
Vasheti
ASKER
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Formula worked after a little tweaking
ASKER
solution worked after tweaking
add_months(trunc(sysdate,'
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.