Can anyone check my syntax please, i am trying to converts Oracle script to SQL server:-
oracle
select TO_DATE('12/15/1970','MM/DD/YYYY')+(DS_PENDING_JOB.END_DATETIME/86400)from DS_PENDING_JOB


while converting to SQL Server:
select convert(date(10),'12/15/1970','mm/dd/yy')+(DS_PENDING_JOB.END_DATETIME/86400)from DS_PENDING_JOB

getting this error:

Server: Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 3 of convert function.

Were you trying to do the following:

select convert(datetime,'12/15/1970',101)

A1 You may wish to consider first converting your data to datetimes, then performing datetime calculations. Some issues with convert (DataType, 'Argument', Style) are as follows:

i There is no DataType designated: "date(10)" note: length may only be specified for nchar, nvarchar, char, varchar, binary, or varbinary convert statements.

ii There is no datetime / smalldatetime Style designation: 'mm/dd/yy'
They are designated as integers: 0,1,2,3,4, ...21 and 100, 101, ...131
Select Convert(SmallDateTime,'12/15/1970', 101)
Select Convert(DateTime,'12/15/1970',101)

Examples:

Select Convert(SmallDateTime,'12/15/1970', 101) As 'SmallDateTime style 101 convert example'
Select Convert(DateTime,'12/15/1970',101)As 'DateTime style 101 convert example'

Select Cast('12/15/1970' As SmallDateTime) As 'SmallDateTime cast example'
Select Cast('12/15/1970' As DateTime)As 'DateTime cast example'


Note, DataTypes are:
bigint
Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
int
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
smallint
Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
tinyint
Integer data from 0 through 255.
bit
Integer data with either a 1 or 0 value.
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
money and smallmoney
money
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
float
Floating precision number data from -1.79E + 308 through 1.79E + 308.
real
Floating precision number data from -3.40E + 38 through 3.40E + 38.
datetime
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
char
Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar
Variable-length non-Unicode data with a maximum of 8,000 characters.
text
Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
nchar
Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar
Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.
ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
binary
Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary
Variable-length binary data with a maximum length of 8,000 bytes.
image
Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
sql_variant
A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.
timestamp
A database-wide unique number that gets updated every time a row gets updated.
uniqueidentifier
A globally unique identifier (GUID)

For future reference, you can use the books online (BOL) for sql server.

Check out the "cast and convert" / "Data Types". Cast and convert will detail the style parameter.

Hi
Thanks for your response – now I got it. As I never worked with SQL server before. Most of our databases are in Oracle, recently we moved some of them to sequel server. I am trying to convert some repositor scripts from Oracle to SQL server. Here I would like to request you to clarify some of my doubts.
For ex : two table TableA and TableB with a columnA and ColumnB respectively. I am trying to join these two tables with a left out join like

Select Table A.columnA, TableB.columnB
From TableA left outer join TableB ON
ColumnA=ColummB.

But my problem is ColumnB = 1 (which is a constant)
If I am giving ColumnB = 1 getting the following error.

Error:Both terms of an outer join must contain columns

How to over come this please explain me, keep in mind I am new to Sequel server.
regards

Can you post the original oracle sql query and the query you tried to convert to sql server ?

Oracle SQL:

SELECT
CASE WHEN GRP6.M_ACTOR_C_NAME IS NOT NULL THEN GRP6.M_ACTOR_C_NAME || '\' END ||
CASE WHEN GRP5.M_ACTOR_C_NAME IS NOT NULL THEN GRP5.M_ACTOR_C_NAME || '\' END ||
CASE WHEN GRP4.M_ACTOR_C_NAME IS NOT NULL THEN GRP4.M_ACTOR_C_NAME || '\' END ||
CASE WHEN GRP3.M_ACTOR_C_NAME IS NOT NULL THEN GRP3.M_ACTOR_C_NAME || '\' END ||
CASE WHEN GRP2.M_ACTOR_C_NAME IS NOT NULL THEN GRP2.M_ACTOR_C_NAME || '\' END ||
CASE WHEN GRP1.M_ACTOR_C_NAME IS NOT NULL THEN GRP1.M_ACTOR_C_NAME || '\' END || ' ' Path,
CASE WHEN GRP1.M_ACTOR_C_NAME IS NULL THEN ' ' ELSE GRP1.M_ACTOR_C_NAME END Group_Name,
OBJ_M_ACTOR.M_ACTOR_C_NAME Actor_name,
CASE WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 1 THEN 'Group'
WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 16 THEN 'User'
WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 128 THEN 'CMDSetting'
ELSE 'Type ' || to_char(OBJ_M_ACTOR.M_ACTOR_N_TYPE)
END Actor_Type,
rtrim(
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 1)) = 1 THEN 'enabled, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 1)) = 0 THEN 'disabled, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 128)) = 128 THEN 'locked, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 2)) = 2 THEN 'locked-2, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 4)) = 4 THEN 'offline prevent, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 8)) = 8 THEN 'cannot change password, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 32)) = 32 THEN 'realtime update, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 64)) = 64 THEN 'cannot delete documents, ' END ||
CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 256)) = 256 THEN 'disable login of pre-4.1.5 release, ' END, ', ') Actor_Status
FROM
OBJ_M_ACTOR GRP1,
OBJ_M_ACTOR GRP2,
OBJ_M_ACTOR GRP3,
OBJ_M_ACTOR GRP4,
OBJ_M_ACTOR GRP5,
OBJ_M_ACTOR GRP6,
OBJ_M_ACTOR,
OBJ_M_ACTORLINK ACTL_2,
OBJ_M_ACTORLINK ACTL_1,
OBJ_M_ACTORLINK ACTL_3,
OBJ_M_ACTORLINK ACTL_4,
OBJ_M_ACTORLINK ACTL_5,
OBJ_M_ACTORLINK ACTL_6,
OBJ_M_ACTORLINK
WHERE
OBJ_M_ACTOR.M_ACTOR_N_LAT <> 1
AND ACTL_1.M_ACTL_N_ACTORID=GRP1.M_ACTOR_N_ID(+)
AND ACTL_1.M_ACTL_N_ACTORTYPE(+)=1
AND ACTL_1.M_ACTL_N_FATLINKID=ACTL_2.M_ACTL_N_ID(+)
AND GRP2.M_ACTOR_N_ID(+)=ACTL_2.M_ACTL_N_ACTORID
AND ACTL_2.M_ACTL_N_ACTORTYPE(+)=1
AND ACTL_2.M_ACTL_N_FATLINKID=ACTL_3.M_ACTL_N_ID(+)
AND GRP3.M_ACTOR_N_ID(+)=ACTL_3.M_ACTL_N_ACTORID
AND ACTL_3.M_ACTL_N_ACTORTYPE(+)=1
AND ACTL_3.M_ACTL_N_FATLINKID=ACTL_4.M_ACTL_N_ID(+)
AND GRP4.M_ACTOR_N_ID(+)=ACTL_4.M_ACTL_N_ACTORID
AND ACTL_4.M_ACTL_N_ACTORTYPE(+)=1
AND ACTL_4.M_ACTL_N_FATLINKID=ACTL_5.M_ACTL_N_ID(+)
AND GRP5.M_ACTOR_N_ID(+)=ACTL_5.M_ACTL_N_ACTORID
AND ACTL_5.M_ACTL_N_ACTORTYPE(+)=1
AND GRP6.M_ACTOR_N_ID(+)=ACTL_6.M_ACTL_N_ACTORID
AND ACTL_6.M_ACTL_N_ACTORTYPE(+)=1
AND ACTL_5.M_ACTL_N_FATLINKID=ACTL_6.M_ACTL_N_ID(+)
AND GRP1.M_ACTOR_N_TYPE(+)=1
AND GRP2.M_ACTOR_N_TYPE(+)=1
AND GRP3.M_ACTOR_N_TYPE(+)=1
AND GRP4.M_ACTOR_N_TYPE(+)=1
AND GRP5.M_ACTOR_N_TYPE(+)=1
AND GRP6.M_ACTOR_N_TYPE(+)=1
AND ACTL_1.M_ACTL_N_LAT(+) <> 1
AND ACTL_2.M_ACTL_N_LAT(+) <> 1
AND ACTL_3.M_ACTL_N_LAT(+) <> 1
AND ACTL_4.M_ACTL_N_LAT(+) <> 1
AND ACTL_5.M_ACTL_N_LAT(+) <> 1
AND ACTL_6.M_ACTL_N_LAT(+) <> 1
AND GRP1.M_ACTOR_N_LAT(+) <> 1
AND GRP2.M_ACTOR_N_LAT(+) <> 1
AND GRP3.M_ACTOR_N_LAT(+) <> 1
AND GRP4.M_ACTOR_N_LAT(+) <> 1
AND GRP5.M_ACTOR_N_LAT(+) <> 1
AND GRP6.M_ACTOR_N_LAT(+) <> 1
AND ACTL_1.M_ACTL_N_ID(+)=OBJ_M_ACTORLINK.M_ACTL_N_FAT LINKID
AND OBJ_M_ACTORLINK.M_ACTL_N_ACTORID(+)=OBJ_M_ACTOR.M_ ACTOR_N_ID
AND OBJ_M_ACTORLINK.M_ACTL_N_LAT(+)<>1
order by 1,2




The sql server query i tryed, but it is not completed.


SELECT
CASE WHEN GRP6.M_ACTOR_C_NAME IS NOT NULL THEN GRP6.M_ACTOR_C_NAME + '\' END +
CASE WHEN GRP5.M_ACTOR_C_NAME IS NOT NULL THEN GRP5.M_ACTOR_C_NAME + '\' END +
CASE WHEN GRP4.M_ACTOR_C_NAME IS NOT NULL THEN GRP4.M_ACTOR_C_NAME + '\' END +
CASE WHEN GRP3.M_ACTOR_C_NAME IS NOT NULL THEN GRP3.M_ACTOR_C_NAME + '\' END +
CASE WHEN GRP2.M_ACTOR_C_NAME IS NOT NULL THEN GRP2.M_ACTOR_C_NAME + '\' END +
CASE WHEN GRP1.M_ACTOR_C_NAME IS NOT NULL THEN GRP1.M_ACTOR_C_NAME + '\' END + ' ' Path,
CASE WHEN GRP1.M_ACTOR_C_NAME IS NULL THEN ' ' ELSE GRP1.M_ACTOR_C_NAME END Group_Name,
OBJ_M_ACTOR.M_ACTOR_C_NAME Actor_name,
CASE WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 1 THEN 'Group'
WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 16 THEN 'User'
WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 128 THEN 'CMDSetting'
ELSE 'Type' + cast(OBJ_M_ACTOR.M_ACTOR_N_TYPE as Decimal)
END Actor_Type,
rtrim(
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 1 THEN 'enabled, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 0 THEN 'disabled, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 128 THEN 'locked, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 2 THEN 'locked-2, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 4 THEN 'offline prevent, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 8 THEN 'cannot change password, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 32 THEN 'realtime update, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 64 THEN 'cannot delete documents, ' END +
CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 256 THEN 'disable login of pre-4.1.5 release,' END) Actor_Status
FROM
OBJ_M_ACTOR GRP1 LEFT outer join OBJ_M_ACTORLINK ACTL_1 ON GRP1.M_ACTOR_N_ID=ACTL_1.M_ACTL_N_ACTORID,
OBJ_M_ACTORLINK ACTL_2 INNER JOIN OBJ_M_ACTORLINK ACTL_1a ON ACTL_2.M_ACTL_N_ID=ACTL_1a.M_ACTL_N_FATLINKID,
OBJ_M_ACTOR GRP2 left outer join OBJ_M_ACTORLINK ACTL_2a on GRP2.M_ACTOR_N_ID=ACTL_2a.M_ACTL_N_ACTORID,
OBJ_M_ACTORLINK ACTL_3a INNER JOIN OBJ_M_ACTORLINK ACTL_2a1 ON ACTL_3a.M_ACTL_N_ID=ACTL_2a1.M_ACTL_N_FATLINKID,
OBJ_M_ACTOR GRP3 left outer join OBJ_M_ACTORLINK ACTL_3 on GRP3.M_ACTOR_N_ID=ACTL_3.M_ACTL_N_ACTORID,
OBJ_M_ACTORLINK ACTL_3a1 INNER JOIN OBJ_M_ACTORLINK ACTL_4 ON ACTL_3a1.M_ACTL_N_ID=ACTL_4.M_ACTL_N_FATLINKID,
OBJ_M_ACTOR GRP4 left outer join OBJ_M_ACTORLINK ACTL_4a on GRP4.M_ACTOR_N_ID=ACTL_4a.M_ACTL_N_ACTORID,
OBJ_M_ACTORLINK ACTL_4a1 INNER JOIN OBJ_M_ACTORLINK ACTL_5 ON ACTL_4a1.M_ACTL_N_ID=ACTL_5.M_ACTL_N_FATLINKID,
OBJ_M_ACTOR GRP5 left outer join OBJ_M_ACTORLINK ACTL_5a on GRP5.M_ACTOR_N_ID=ACTL_5a.M_ACTL_N_ACTORID,
OBJ_M_ACTOR GRP6 left outer join OBJ_M_ACTORLINK ACTL_6a on GRP6.M_ACTOR_N_ID=ACTL_6a.M_ACTL_N_ACTORID,
OBJ_M_ACTORLINK ACTL_5a1 INNER JOIN OBJ_M_ACTORLINK ACTL_6 ON ACTL_5a1.M_ACTL_N_ID=ACTL_6.M_ACTL_N_FATLINKID,
OBJ_M_ACTORLINK ACTL_7 left outer join OBJ_M_ACTORLINK ON ACTL_7.M_ACTL_N_ID=OBJ_M_ACTORLINK.M_ACTL_N_FATLIN KID,
OBJ_M_ACTORLINK OA LEFT OUTER JOIN OBJ_M_ACTOR ON OA.M_ACTL_N_ACTORID=OBJ_M_ACTOR.M_ACTOR_N_ID
WHERE
OBJ_M_ACTOR.M_ACTOR_N_LAT<>1
AND OBJ_M_ACTORLINK.M_ACTL_N_LAT<>1
ORDER BY 1,2


thanks.

Sorry I asked I will try to get back to you in a few hours.

Which ones are constants ? When you say constants where are they being defined and what are their values ? Are they not part of a table ? Give me a simple example of the data in the table and the constants of what it would look like before and after the query. How does oracle treat a left outer join that has a constant - what does it return ?

Basically there are only two tables OBJ_M_ACTOR and OBJ_M_ACTORLINK having a PK link between M_ACTOR_N_ID column with 100 rows from OBJ_M_ACTOR table and M_ACTL_N_ACTORID column with 200 rows from OBJ_M_ACTORLINK. As we know in Oracle we can through an outer join on a column with less number of rows.
My tables values are like this:

OBJ_MACTOR table
M_ACTOR_N_STATUS || M_ACTOR_N_ID ||M_ACTOR_N_LAT
1 1 4
1 2 4
1 3 4
1 4 4
OBJ_M_ACTORLINK Table
M_ACTL_N_ACTORID || M_ACTL_N_ID || M_ACTL_N_FATLINKID
1 1 1
2 2 2
3 3 2
4 3 3


select a.M_ACTOR_N_STATUS,a.M_ACTOR_N_ID,b.M_ACTL_N_ACTOR ID,b.M_ACTL_N_ACTORTYPE
from OBJ_M_ACTOR a,
OBJ_M_ACTORLINK b
where
b.M_ACTL_N_ACTORID=a.M_ACTOR_N_ID(+)
and b.M_ACTL_N_ACTORTYPE(+)=1



Probably I may confused you by mentioning constant, in my talk constant means the value refer in where condition that is: b.M_ACTL_N_ACTORTYPE(+)=1 in my case. You can get better idea by look at the sample script above. But in the original script they used different alias for the same table you may notice that.
OBJ_M_ACTOR GRP1,
OBJ_M_ACTOR GRP2,
OBJ_M_ACTOR GRP3,
OBJ_M_ACTOR GRP4,
OBJ_M_ACTOR GRP5,
OBJ_M_ACTOR GRP6,
OBJ_M_ACTOR,
OBJ_M_ACTORLINK ACTL_2,
OBJ_M_ACTORLINK ACTL_1,
OBJ_M_ACTORLINK ACTL_3,
OBJ_M_ACTORLINK ACTL_4,
OBJ_M_ACTORLINK ACTL_5,
OBJ_M_ACTORLINK ACTL_6,
OBJ_M_ACTORLINK
And also there is one join (like self join) on the same table of different aliases ex:
ACTL_1.M_ACTL_N_FATLINKID=ACTL_2.M_ACTL_N_ID(+)
Hope I explained clearly.