| Convert code from SQL to SQL Plus [message #72186] |
Tue, 08 April 2003 10:54  |
Intikhab Bashir
Messages: 1
Registered: April 2003
|
Junior Member |
|
|
Hi, how can I change the following so Oracle SQL can recognise.
Thanks in advance:
-------------------DATE-------------------------
declare @year varchar(4)
set @year = datepart(yy, getdate())
declare @month varchar(2)
set @month = datepart(month, getdate())
if (@month) = '1'
begin
set @month = '12'
set @year = @year -1
end
else
set @month = @month - 1
declare @monthName varchar(3)
declare @day varchar(2)
if @month = '1'
begin
set @day = '31'
set @monthName = 'Jan'
end
else if @month = '3'
begin
set @day = '31'
set @monthName = 'Mar'
end
else if @month = '4'
begin
set @day = '30'
set @monthName = 'Apr'
end
else if @month = '5'
begin
set @day = '31'
set @monthName = 'May'
end
else if @month = '6'
begin
set @day = '30'
set @monthName = 'Jun'
end
else if @month = '7'
begin
set @day = '31'
set @monthName = 'Jul'
end
else if @month = '8'
begin
set @day = '31'
set @monthName = 'Aug'
end
else if @month = '9'
begin
set @day = '30'
set @monthName = 'Sep'
end
else if @month = '10'
begin
set @day = '31'
set @monthName = 'Oct'
end
else if @month = '11'
begin
set @day = '30'
set @monthName = 'Nov'
end
else if @month = '12'
begin
set @day = '31'
set @monthName = 'Dec'
end
else
begin
set @day = '28'
set @monthName = 'Feb'
end
declare @date varchar(20)
set @date = @monthName + ' ' + right(@year, 2)
declare @from varchar(30)
declare @to varchar(30)
set @from = '01 ' + @date
set @to = @day + ' ' + @date
print @from
print @to
|
|
|
|
| Re: Convert code from SQL to SQL Plus [message #72206 is a reply to message #72186] |
Sat, 12 April 2003 04:27  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It is amazing how much lengthy complicated code is used in some non-Oracle systems to retrieve such simple results. It looks like all that code does is retrieve the starting and ending dates of the previous month in a specific format. Below are a couple of simple ways to get the same results in Oracle, using either SQL or PL/SQL. The code below does all the things that your code does, including using December of the previous year if the original month is January, getting the correct number of days based on which month, and so forth. The code can either be typed at the SQL> prompt in SQL*Plus or saved as a .sql file, then started. Please let me know if there is something else required, that I am not seeing.
For future reference, this sort of question belongs in either the SQL or PL/SQL discussion forums, not in Server Utilities, since it has nothing to do with server utilities, unless you were hoping that there is some sort of utility to automatically convert such things, which there isn't.
SQL> -- SQL:
SQL> SELECT TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-1),'MM'),'DD Mon YY') dates
2 FROM DUAL
3 UNION ALL
4 SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD Mon YY') dates
5 FROM DUAL
6 /
DATES
---------
01 Mar 03
31 Mar 03
2 rows selected.
SQL> --
SQL> -- or:
SQL> --
SQL> -- PL/SQL:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE
3 (TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-1),'MM'),'DD Mon YY'));
4 DBMS_OUTPUT.PUT_LINE
5 (TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD Mon YY'));
6 END;
7 /
01 Mar 03
31 Mar 03
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
