oracle关于时间的处理，如计算间隔天数、获取本年第一天、上月第一天、上月最后一天

落日映苍穹つ 2021-09-27 13:28 500阅读 0赞

一、

select sysdate from dual;
    SELECT APPLY_DATE ,round((select sysdate from dual)-APPLY_DATE,0) from DT_AUDIT_UPDATE ;
    SELECT APPLY_DATE ,round((select sysdate from dual)-APPLY_DATE,0) len ,a.* from DT_AUDIT_UPDATE a
    where a.STATUS ='1' and round((select sysdate from dual)-APPLY_DATE,0)>60;

注：[https://www.cnblogs.com/AnneHan/p/4708386.html][https_www.cnblogs.com_AnneHan_p_4708386.html]

[https://www.cnblogs.com/powerwu/articles/5579406.html][https_www.cnblogs.com_powerwu_articles_5579406.html]

二、

select trunc(时间1- 时间2) from table\_name;

时间1和时间2必须是date类型 如果不是必须to\_date;

注：[https://blog.csdn.net/gahaya/article/details/8148337][https_blog.csdn.net_gahaya_article_details_8148337]

三、

格式化日期：     TO\_CHAR(SYSDATE(),'YY/MM/DD HH24:MI:SS)

eg:

select TO_CHAR(a.last_login_time,'YY/MM/DD HH24:MI:SS') ,a.*  from JC_USER a where a.USERNAME ='admin'

四、获取本年第一天、上月第一天、上月最后一天

--今年一月一日
    select to_char(to_date(concat((select to_char(sysdate,'yyyy') from dual), '-01-01 00:00:00'),'yyyy-MM-dd HH24:mi:ss'),'yyyy-mm-dd') from dual; 
    --上个月最后一天
    select to_char(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'yyyy-mm-dd') from dual;
    --上个月第一天
    SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1,'yyyy-mm-dd')  FROM DUAL;

[https://juejin.cn/post/6844903929784827911][https_juejin.cn_post_6844903929784827911]

[https_www.cnblogs.com_AnneHan_p_4708386.html]: https://www.cnblogs.com/AnneHan/p/4708386.html
[https_www.cnblogs.com_powerwu_articles_5579406.html]: https://www.cnblogs.com/powerwu/articles/5579406.html
[https_blog.csdn.net_gahaya_article_details_8148337]: https://blog.csdn.net/gahaya/article/details/8148337
[https_juejin.cn_post_6844903929784827911]: https://juejin.cn/post/6844903929784827911