CODEWITHSUNDEEP
oracle-database
Des Hutch
Des Hutch

Reputation: 293

Oracle equivalant of MS SQL Date Query

I'm trying to run the query below on an Oracle server. I have tried many iterations and a few hours lost so far with no success. What would be the equivalent in Oracle that would Print the result of the equation?

Declare @WeekendingDay varchar(10)
Declare @DayNumber int
Declare @InputDate varchar(10)

Declare @conInputDate datetime
Declare @outWeekending datetime
Declare @CovertToInt varchar(10)
/* ------------------------------Inputs ------------------------------ */
/* uncomment the weekending day you want */
--Set @WeekendingDay = 'Monday'
--Set @WeekendingDay = 'Tuesday'
Set @WeekendingDay = 'Wednesday'
--Set @WeekendingDay = 'Thursday'
--Set @WeekendingDay = 'Firday'
--Set @WeekendingDay = 'Saturday'
--Set @WeekendingDay = 'Sunday'

/* Date you want the weekending of */                       
Set @InputDate = '29/12/2016'

/* Yes to convert Date as INT, No for normal date output */
Set @CovertToInt = 'No'

/* --------------------♫♫♫…Start the magical dance…♫♫♫-------------------- */
Set @DayNumber = CASE @WeekendingDay
              WHEN 'Sunday' Then 1
              WHEN 'Monday' THEN 2
              WHEN 'Tuesday' THEN 3
              WHEN 'Wednesday' THEN 4
              WHEN 'Thursday' THEN 5
              WHEN 'Friday' THEN 6
              WHEN 'Saturday' THEN 7
        END


Set @conInputDate = CONVERT(datetime,@InputDate,103)
Set @outWeekending = DATEADD (dd, case when DatePart (DW, @conInputDate)=@DayNumber then 0 else -1 * DatePart (DW, @conInputDate) + 7 + @DayNumber end ,@conInputDate)

/* ------------------------------Outputs------------------------------*/
If @CovertToInt = 'Yes'
  Begin 
  PRINT convert(int, convert(varchar(10), @outWeekending, 112))
  END

If @CovertToInt = 'No'
  Begin
  PRINT @outWeekending 
  END

Oracle Attempt so far

Declare
DayNumber Number(6);
InputDate varchar2(10);
conInputDate date;
outweekending date;

BEGIN
DayNumber := 4;
InputDate := '29/12/2016';
conInputdate := to_date(Inputdate, 'dd/mm/yyyy');
outweekending := DATEADD (dd, case when DatePart (DW, conInputDate)= DayNumber then 0 else -1 * DatePart (DW, conInputDate) + 7 + DayNumber end , conInputDate)

dbms_output.put_line(outweekending);
END

Upvotes: 3

Views: 62

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Try this out:

set serveroutput on;
declare
    day varchar(20) := 'Wednesday';
    dt date := to_date('29-12-2016','dd-mm-yyyy');
    next_dt date;
    convert_to_int varchar(10) := 'No';
begin
    next_dt := next_day(dt - 1, day);
    if convert_to_int = 'Yes' then
        dbms_output.put_line(to_char(next_dt,'yyyymmdd'));
    else
        dbms_output.put_line(next_dt);
    end if;
end;
/

Upvotes: 1

Related Questions