Could you please help me to correct the syntax ? I have to write below code in SQL server. This is working perfectly fine in Oracle database.
Select to_number(substr((((END_TS - BEGIN_TS)*(24*60*60))),1,10)) AS EXECUTION_TIME
from TABLE B
Also END_TS and BEGIN_TS are of datetime datatypes.
In SQL Server math can not be performed directly on dates, as it can in Oracle. You need to apply a datediff function to calculate the difference before you manipulate it:
select convert(numeric(10,9),left(datediff(second,begin_ts,end_ts)/(24.0*60*60),10)) from table;
Note that the expression in the divisor needs to have a floating point number in it (hence the ".0") otherwise the result is rounded to an integer.
After performing the date calculation, the left function is the equivalent of the substring in Oracle. It converts to a varchar then takes the first 10 characters. Convert then returns to a numeric, which is the equivalent of Oracle's variable-length number. It is necessary to tell convert that you expect digits after the decimal, otherwise it will round.
The substring for the first 10 characters has a bad smell, I would leave it out. This snippet does the calculation without restricting to the first ten characters.
select datediff(second,begin_ts,end_ts)/(24.0*60*60) from table;
Also note that the Oracle version provides fractional dates. If you only wanted the whole day then use "day" as the datepart parameter to datediff.