How do i find employee who are hired 6 weeks ago from curren

How do i find employee who are hired 6 weeks ago from current running weeks?

My current code executes a query in access DB and filters the data based on WeekNumber Column given by below query..

IIf(Weekday([HiredDate])=7,[HiredDate],[HiredDate]-Weekday([HiredDate])) AS TrainingStart,
Date()+6-Weekday(Date()) AS EndOfWeek,
DateDiff("ww",[TrainingStart],[EndOfWeek]) AS WeekNumber

We do not have access db anymore and i need to get the data from Oracle db.

How do I write to get same data in this situation?

Hey try this

In Sql Server:

DECLARE @HiredDate DATETIME='2019-07-26'
SELECT *, DateDiff("WW",TrainingStart,EndOfWeek) AS WeekNumber FROM(
Select CASE(DATEPART(dw,@HiredDate)) WHEN 7 THEN @HiredDate
ELSE DATEADD(DD,-DATEPART(dw,@HiredDate),@HiredDate) END AS TrainingStart,
GETDATE() + 6 - DATEPART(dw,GETDATE()) AS EndOfWeek) AS tblNew

In Oracle:

v_HiredDate TIMESTAMP(3):='2019-07-26'
SELECT *, DateDiff("WW",TrainingStart,EndOfWeek) AS WeekNumber FROM(
Select CASE(TO_NUMBER(TO_CHAR(v_HiredDate, 'dw'))) WHEN 7 THEN v_HiredDate
ELSE -DATEPART * INTERVAL '1' DD(5)(dw,v_HiredDate) +v_HiredDate END AS TrainingStart,
SYSTIMESTAMP + 6 - TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'dw')) AS EndOfWeek From dual) AS tblNew

Hey try this

In Sql Server:

DECLARE @HiredDate DATETIME='2019-07-26'
SELECT *, DateDiff("WW",TrainingStart,EndOfWeek) AS WeekNumber FROM(
Select CASE(DATEPART(dw,@HiredDate)) WHEN 7 THEN @HiredDate
ELSE DATEADD(DD,-DATEPART(dw,@HiredDate),@HiredDate) END AS TrainingStart,
GETDATE() + 6 - DATEPART(dw,GETDATE()) AS EndOfWeek) AS tblNew

In Oracle:

v_HiredDate TIMESTAMP(3):='2019-07-26'
SELECT *, DateDiff("WW",TrainingStart,EndOfWeek) AS WeekNumber FROM(
Select CASE(TO_NUMBER(TO_CHAR(v_HiredDate, 'dw'))) WHEN 7 THEN v_HiredDate
ELSE -DATEPART * INTERVAL '1' DD(5)(dw,v_HiredDate) +v_HiredDate END AS TrainingStart,
SYSTIMESTAMP + 6 - TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'dw')) AS EndOfWeek From dual) AS tblNew

How do i find employee who are hired 6 weeks ago from curren

1 Reply

How do i find employee who are hired 6 weeks ago from curren

1 Reply