ORACLE日期和时间的相关查询

2024-10-14 13:37:23

1、在session 中修改时间格式：alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

2、获取当前月份的第一天：SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL;

3、获取当前年份的第一天：SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

4、获取当前年份的最后一天：SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL;

5、获取当前月份的天数：SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days FROM DUAL;

6、获取当前月份剩下的天数：SELECT SYSDATE, LAST_DAY (SYSDATE) "Last", LAST_DAY (SYSDATE) - SYSDATE "Days left" FROM DUAL;

7、获取两个日期之间的天数：alter session set NLS_DATE_LANGUAGE='AMERICAN';SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0) num_of_daysFROM DUAL;

8、获取直到目前为止今天过去的秒数（从 00：00 开始算）：SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning FROM DUAL;

9、获取今天剩下的秒数（直到 23：59：59 结束）：SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL;