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
Who is Participating?
First one should be something like:
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.
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.
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).
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.
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/201
FROM dual
CONNECT BY LEVEL <= to_date('02/28/2011') - to_date('12/31/2010')
)
where day_of_week not in (1,7)
/