sql - ORACLE SQL CAST IN CASE 语句

Question

有没有办法在 CASE 语句中将 CAST 转换为 INTEGER？

CASE 
WHEN SV.END_DATE IS NOT NULL THEN SV.END_DATE - SV.START_DATE 
ELSE 0
END AS DAYDIFF

Answer 1

是的，你可以 cast不带小数部分的数字数据类型的值:

CASE 
  WHEN SV.END_DATE IS NOT NULL THEN
    CAST(SV.END_DATE - SV.START_DATE AS NUMBER(38))
  ELSE 0
END AS DAYDIFF

或者你可以truncate the result ，它还会去除值的小数部分:

CASE 
  WHEN SV.END_DATE IS NOT NULL THEN
    TRUNC(SV.END_DATE - SV.START_DATE)
  ELSE 0
END AS DAYDIFF