select decode(chgdt,'',查询日期-depdt,查询日期-depdt) oltime from tablename
我先试试,我的日期类型都是char的,不是date 的,估计要先加to_date转化
select nvl2(chgdt,查询日期-chgdt,查询日期-depdt) oltime from tablename
可以的,谢谢2位。 如果要用oltime排序的话,似乎不可以 要通过select嵌套,是吧?
对,就是用子查询: select * from ( select sysdate - nvl(chgdt,depdt) as oltime from tabname ) A order by oltime不过也可以这样写: select sysdate - nvl(chgdt,depdt) as oltime from tabname order by (sysdate - nvl(chgdt,depdt))
使用nvl和nvl2都可以,不过我还是有错误, ERROR: ORA-01841: (full) year must be between -4713 and +9999, and not be 0而我能够确定chgdt是没有空值的 真怪了
select case when chgdt is not null then sysdate-to_date(chgdt,'YYYYMMDD') else sysdate-to_date(depdt,'YYYYMMDD') end as outline from table
ERROR: ORA-01841: (full) year must be between -4713 and +9999, and not be 0应该是to_date()出错了,检查的格式是否匹配
不用子查询可以直接排序:select nvl2(chgdt,查询日期-chgdt,查询日期-depdt) oltime from tablename order by oltime
如果要用oltime排序的话,似乎不可以
要通过select嵌套,是吧?
select * from
(
select sysdate - nvl(chgdt,depdt) as oltime from tabname
) A order by oltime不过也可以这样写:
select sysdate - nvl(chgdt,depdt) as oltime
from tabname
order by (sysdate - nvl(chgdt,depdt))
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0而我能够确定chgdt是没有空值的
真怪了
else sysdate-to_date(depdt,'YYYYMMDD') end as outline from table
ORA-01841: (full) year must be between -4713 and +9999, and not be 0应该是to_date()出错了,检查的格式是否匹配
难怪会出错了
数据又不能修改的。用decode倒可以解决,没有报错
to_date(ltrim(chgdt),fmt)
排序是不可以的,我忘记说了,对oltime还有最大最小值的限制的