MS/SQL to PL/SQL

[JuanSanchez]

I'm trying to convert this T-SQL to PL/SQL. This is part of a view on my MS SQL 2k server. But I need to use it in a DTS package to pull data from our Oracle server. I'm trying to get the weekending 2 weeks previous. Weeks end on saturday in this script. Thanks in advance. The server does not have the data till Wednesday if your wondering about the numbers jumping up.

select
convert(varchar(12),dateadd(ww, -2, dateadd(dd, 7 - (case datename(dw, getdate())
when 'Sunday' then 8 
when 'Monday' then 9 
when 'Tuesday' then 10
when 'Wednesday' then 4 
when 'Thursday' then 5 
when 'Friday' then 6 
else 7 end), getdate())),101)

 

[SantaMufasa]

Juan,

I'm sure we can help you, but since I am not as MS SQL proficient as I should be, could you just offer a functional narrative of what you want your code to do?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:44 (08Dec03) GMT, 14:44 (08Dec03) Mountain Time)

 

[JuanSanchez]

Thanks Santa,

Im looking to generate the saturday date from 2 weeks previous.
BUT
Wednesday is the cut off for the previous weeks

so 12/03/03 thru 12/09/03 you would pull 11/22/03
but
on 12/10/03 thru 12/16/03 you would pull 11/29/03

Any help would be great.

Thanks again,
Sanchez

 

[vc123]

Hello,

You can do this so:

select trunc(sysdate-10, 'd')-1 from dual;

E.g.:

select x, trunc(x-10, 'd')-1 from (
select to_date('12/2/03', 'mm/dd/yy') + rownum x from user_objects where rownum<=14);

X TRUNC(X-10,'D')-1
12/3/2003 11/22/2003
12/4/2003 11/22/2003
12/5/2003 11/22/2003
12/6/2003 11/22/2003
12/7/2003 11/22/2003
12/8/2003 11/22/2003
12/9/2003 11/22/2003
12/10/2003 11/29/2003
12/11/2003 11/29/2003
12/12/2003 11/29/2003
12/13/2003 11/29/2003
12/14/2003 11/29/2003
12/15/2003 11/29/2003
12/16/2003 11/29/2003

Rgds.

 

[SantaMufasa]

Juan,

Here is a PL/SQL function that does what you want:

create or replace function Sat2ago (nowdate in date) return date is
	date_hold	date;
begin
	select decode	(to_char(nowdate,'Dy') 
		,'Wed',next_day(next_day(nowdate+1,'Wed')-21,'Sat')
		,next_day(next_day(nowdate,'Wed')-21,'Sat'))
	into date_hold
	from dual;
	return date_hold;
end;
/
Sample usage:
col a heading "Start Date" format a14
col b heading "2 Saturdays|earlier" format a14
select	 to_char(sysdate-6,'Dy, dd-Mon-YY') a
	,to_char(Sat2ago(sysdate-6),'Dy, dd-Mon-YY') b
	from dual;

               2 Saturdays
Start Date     earlier
-------------- --------------
Tue, 02-Dec-03 Sat, 15-Nov-03

               2 Saturdays
Start Date     earlier
-------------- --------------
Wed, 03-Dec-03 Sat, 22-Nov-03

               2 Saturdays
Start Date     earlier
-------------- --------------
Mon, 08-Dec-03 Sat, 22-Nov-03

               2 Saturdays
Start Date     earlier
-------------- --------------
Wed, 10-Dec-03 Sat, 29-Nov-03

               2 Saturdays
Start Date     earlier
-------------- --------------
Tue, 16-Dec-03 Sat, 29-Nov-03

               2 Saturdays
Start Date     earlier
-------------- --------------
Wed, 17-Dec-03 Sat, 06-Dec-03

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:15 (09Dec03) GMT, 18:15 (08Dec03) Mountain Time)