how do i change this oracle sql code to t-sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how do i change this oracle sql code to t-sql

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-09-17 : 15:03:29

hi there how would i change the following oracle sql code to t-sql.

select
NEXT_DAY(((TO_DATE(T1."DDATE" ,'yyyymmdd' )) + (-1)),'Sunday'),
T2."TWARD",
DECODE(LEAST(RTRIM(T1."DTIME" ) ,'11:00:00'),
NULL,
0 ,
RTRIM(T1."DTIME" ),1,0), T1."DURNO"
from
"IBA"."LIVE_PATDSCHF" T1,
"IBA"."LIVE_PATMI1AF" T3,
"IBA"."LIVE_PATTRANF" T2
where
T3."DAADMNO" = T2."DTADMN"(+)
and
T2."TWARD"
in (
'ADA',
'ALE',
'AMO',
'BOB',
'CAT',
'DOL',
'ELI',
'EMU',
'FEL',
'CCU',
'LYD',
'MAR',
'MEL',
'NAS',
'SAU',
'VIC'
)
and
NEXT_DAY(((TO_DATE(T1."DDATE" ,'yyyymmdd' )) + (-1)),'Sunday')>=to_date('2000-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and DECODE(T2."TMOVE",'A','Admission' ,'D','Discharge','C','Change Rec.','L','On Leave','R','Ret. Leave','** Invalid **')='Discharge'
and T3."DAADMNO"=T1."DDADMNO"

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 15:07:48

I don't think there are any "regulars" here who have much knowledge of Oracle, so if you don't get an answer you may want to try at dbForums

Kristen

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-17 : 15:23:45

You would probably be better off posting the requirement and the table data, there is also a migration tool for Pl/Sql to Tsql at:

http://www.microsoft.com/sql/solutions/migration/default.mspx

I know that DECODE() has to be replaced with CASE, I am not sure of anything that replaces NEXT_DAY(). TO_DATE may not be necessary at all but if conversion is required it will be CONVERT() or CAST().

Future guru in the making.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 01:48:23

There is no equivalent for NEXT_DAY() in sql server. You need to have dates table and then make use of a function that returns next sunday based on the sepcific date

Madhivanan

Failing to plan is Planning to fail

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-18 : 02:30:45

Here is some code for a NEXT_DAY type function for SQL Server. From http://www.devx.com/getHelpOn/10MinuteSolution/16499/1954

create function fn_getnextdate(@currdate datetime, @nextday int)
returns datetime

as
begin
/*******************************
returns the date of the next weekday (specified by @nextday with 1 = Sun. 
2 = Mon etc.) after the current date. Used to find the 'Friday after' etc.

*********************************/
declare @fn_getnextdate datetime,
    @cday int


set @cday = datepart(dw,@currdate)

if @cday < @nextday
   select @fn_getnextdate = dateadd(dd,abs(@cday - @nextday), @currdate)
else
   select @fn_getnextdate = dateadd(dd,7 - @cday + @nextday, @currdate)
   
return (@fn_getnextdate)
end

Google is your friend..

-ec

Kristen
Test

22859 Posts

Posted - 2007-09-18 : 02:39:42

Hehehe ... the one thing that MVJ's function doesn't do.

"that is something I left out intentionally, because I wasn't sure that everyone had the same idea about what a week day was."

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources