I need to determine weeknumbers from two dates.
I have a query in Access DB which looks like this. I am trying to achieve same query in Oracle. How do i do it? I have written a query but it does not seem like right.
IIf(Weekday([HiredDate])=7,[HiredDate],[HiredDate]-Weekday([HiredDate])) AS TrainingStart,
Date()+6-Weekday(Date()) AS EndOfWeek,
DateDiff("ww",[TrainingStart],[EndOfWeek]) AS WeekNumber,
What I have tried:
SELECT Name, HiredDate,
(CASE WHEN (to_char(to_date(HiredDate), 'd') = 7) THEN HiredDate ELSE (HiredDate - to_char(to_date(HiredDate), 'd')) END) as StartDate,
(CURRENT_DATE + (6 - to_char(to_date(CURRENT_DATE), 'd'))) as EndDate,
(To_Number(to_char(to_date((CURRENT_DATE + (6 - to_char(to_date(CURRENT_DATE), 'd')))),'WW')) - To_number(to_char(to_date((HiredDate - to_char(to_date(HiredDate), 'd'))),'WW')) +1) as WEEKNUMBER
FROM employee;