How to get number of days without using TRUNC in Oracle

问题: I am trying to get the number of days between SYSDATE and a column in one of my tables without using TRUNC since I need the timestamp to be included within the subtraction....

问题:

I am trying to get the number of days between SYSDATE and a column in one of my tables without using TRUNC since I need the timestamp to be included within the subtraction.

I am trying to achieve something similar to the code below but without using TRUNC

select * from table_name where trunc(sysdate) - trunc(column_name) > 60;

Note that column_name is a Timestamp not a Date.


回答1:

Use an INTERVAL datatype to define an interval. to_dsinterval() converts a string of the format `DD HH24:MI:SS' to an interval, in this case of exactly sixty days:

select * from table_name 
where column_name < sysdate - to_dsinterval('60 0:0:0');

回答2:

You could use:

select * from table_name where column_name <= sysdate - 60;

or:

SELECT * FROM table_name WHERE column_name < sysdate - interval '60' day;

回答3:

Why don't you try select req_columns from MyTable where sysdate - column > 60.

You can try below query if one of the column is timestamp.

select * from Mytable where sysdate - to_date(to_char(colTimeStamp, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') > 60
  • 发表于 2019-02-22 05:25
  • 阅读 ( 226 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除