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