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.
你能帮我纠正一下语法吗？我必须在SQL server中编写下面的代码。这在Oracle数据库中运行得非常好。
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.
END_TS和BEGIN_TS也是datetime数据类型。
1 个解决方案
#1
0
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:
在SQL Server中，数学不能直接在日期上执行，就像在Oracle中一样。在操作它之前，您需要应用datediff函数来计算差异：
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.
请注意，除数中的表达式需要有一个浮点数（因此为“.0”），否则结果将四舍五入为整数。
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.
执行日期计算后，左侧函数相当于Oracle中的子字符串。它转换为varchar然后获取前10个字符。转换然后返回一个数字，这相当于Oracle的可变长度数字。有必要告诉转换，你期望小数点后的数字，否则它将圆。
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.
前10个字符的子字符串有难闻的气味，我会把它留下来。此代码段执行计算而不限制前十个字符。
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.
另请注意，Oracle版本提供了小数日期。如果你只想要一整天，那么使用“day”作为dateiff参数。
#1
0
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:
在SQL Server中，数学不能直接在日期上执行，就像在Oracle中一样。在操作它之前，您需要应用datediff函数来计算差异：
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.
请注意，除数中的表达式需要有一个浮点数（因此为“.0”），否则结果将四舍五入为整数。
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.
执行日期计算后，左侧函数相当于Oracle中的子字符串。它转换为varchar然后获取前10个字符。转换然后返回一个数字，这相当于Oracle的可变长度数字。有必要告诉转换，你期望小数点后的数字，否则它将圆。
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.
前10个字符的子字符串有难闻的气味，我会把它留下来。此代码段执行计算而不限制前十个字符。
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.
另请注意，Oracle版本提供了小数日期。如果你只想要一整天，那么使用“day”作为dateiff参数。