Oracle 日期处理函数+常用函数示例

参考自：原文链接（非常详细）

时间相关应用示例————

1. select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;——2019-10-21 14:34:15

2. select to_char(sysdate,'ddd-day-dy-Q-WW-W') from dual;——294-星期一-4-42-3（当年第几天-星期几-星期几缩写-第几季度-当年第几周-当月第几周）

3. select to_date('2019-10-21 14:34:15','yyyy-mm-dd hh24:mi:ss') from dual；——字符转换为日期格式

4. select to_char (sysdate, 'yy-mm-dd-day-dy', 'NLS_DATE_LANGUAGE = American') from dual;——19-10-21-monday   -mon   （以英文显示日期）

5. select * from nls_session_parameters;——查看系统支持的日期格式

6. select to_number(sysdate - (sysdate-1))*24*60*60*1000 from dual;——86400000（相差多少天、时、分、秒、毫秒，相应增删乘数即可）

7. select to_date(null) from dual;——如果时间为null，那么null也要转换为时间格式

8. select to_char(add_months(trunc(sysdate),-1),'yyyy-mm') from dual;——2019-09（上个月）

9. select last_day(add_months(trunc(sysdate),-1)) from dual;——2019/9/30（当月最后一天）

10. select to_char(trunc(add_months(trunc(sysdate),-1),'month'),'yyyy-mm-dd HH24:MI:SS') from dual;——2019-09-01 00:00:00（上个月第一天）

11. select next_day(sysdate,7) from dual;——2019/10/26 15:16:43（从输入日期得当下个星期天的日期，7可以换成其他星期数）

12. select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-3-20','yyyy-mm-dd')) months from dual;——0.032258064516129（两个月差）

13. select extract(month from sysdate) from dual;——10（截取时间戳的某个字段，语法为extract(year|month|day|hour|minute|second from column_name)）

14. select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual;——365（计算当年有多少天）

15. select to_char(last_day(to_date('02','mm')),'yyyy-mm-dd') from dual;——2019-02-28（获取当年2月最后一天信息，判断是否闰年）

---

时间常用函数总结：

• select SYSDATE from dual;——2019/10/21 16:07:37（获取系统时间）
• Select last_day(sysdate) from dual;——2019/10/31 16:09:19（获取输入date类型的最后一天）
• Select add_months(sysdate,2) from dual;——2019/12/21 16:12:29（左参数日期，右参数当前月份加减数，获取相差月份的日期）
• select months_between(sysdate,to_date('2019-09-12','yyyy-mm-dd'))from dual;——1.31224499701314（日期相差的月份）
• SELECT next_day(sysdate,2) FROM dual;——2019/10/28 16:20:17（下周2日期）
• select sessiontimezone,current_date from dual;——+08:00，2019/10/21 16:21:53（返回时区和当前会话时区中的当前日期）
• select extract(month from sysdate) "This Month" from dual;——10（按规则得到时间字段，extract(year|month|day|hour|minute|second from column_name) = value）
• SELECT   TRUNC (SYSDATE, 'DD'),TRUNC (SYSDATE, 'MM'),TRUNC (SYSDATE, 'yyyy'),TRUNC (SYSDATE, 'day'),TRUNC (SYSDATE, 'q') FROM DUAL;——（截取：当天、本月第一天、本年第一天、本周第一天、本季度第一天；另外还可以截取时分秒）

其他常用函数总结：

• select concat('010-','88888888') from dual;——010-88888888（连接字符串，oracle的concat函数不能连接超过两个，三个及以上需要嵌套使用，或者用管道符||进行连接）
• select substr('12345',3,2) from dual——34（截取字符串，下标基1，从3开始截取两个字符）;
• select instr('oracle traning','ra',1,2)  from dual;——9（从1开始查找第2个出现的'ra'字符串，返回下标，若找不到则返回0）
• select initcap('smith hEllo') from dual;——Smith Hello（格式化字符串，使单词首字符大小，其余小写）
• select lpad(rpad('gao',10,'*'),17,'*')from dual;——*******gao*******（在指定字符的左或右拼接指定符号，使总长度达到指定的数字）
• select replace('he love you','he','I') from dual;——I love you（用指定字符替换相应字符）
• select trim('a' from 'abacda') from dual;——bacd（用单字符删除首尾第一个字符，若匹配则删除）
• select trim('      abacd       ') from dual;——（删除首位空格）
• select ceil(3.1415927) from dual;——4（取整，大于自身）
• select floor(3.1415927) from dual;——3（取整，不小于自身）
• select round(55.655, 2) from dual；——55.66（四舍五入取整，保留小数点右边两位，若是负数则保留小数点左边）
• select trunc (55.655, 2) from dual;——（截取，只舍不入）
• select abs(-100) from dual;——100（获取绝对值）
• SELECT LENGTH (' ') FROM DUAL;——（返回字符串长度，空则为空）
• select lower('AaBbCcDd') from dual;——（变小写）
• select upper('AaBbCcDd') from dual;——（变大写）
• select ASCII('s') from dual;——（返回字符的十进制ascii码）
• select xm from table1 where soundex(xm)=soundex('weather');——（查找发音类似的字符串）
• select mod(10,3) from dual;——1（求余数）
• select power(3,3) from dual;——27（求次方）
• select sign(123),sign(-100),sign(0) from dual;——1，-1,0（检查正负）
• select sqrt(64),sqrt(10) from dual;——（计算平方根）
• Select ASCIISTR ('中国') from dual;——（转换数据库字符集的ASCII码）
• Select decode('a','金',1,'银',2,0) from dual;——0（用a跟后面参数进行比对，如果是比对成功就显示该值，否则显示默认值0）
• Select nullif('a', 'b') from dual;——a（比较两个表达式，相等返回null，否则返回第一个结果）
• Select nvl(null,0) from dual;——0（如果第一个参数为空，则返回第二个参数）
• Select nvl2(null,1,0) from dual;——0（不为空则返回第二个参数，为空则返回第三个参数）
• 分组统计函数：
  • AVG([DISTINCT|ALL]col)——求平均值
  • COUNT(*|[DISTINCT|ALL] col)——求总数
  • MAX([DISTINCT|ALL]col)——求最大值
  • MIN([DISTINCT|ALL]col)——求最小值
  • SUM([DISTINCT|ALL]col)——求和
• 获取数据库信息：
  • 　　

    SELECT SYS_CONTEXT ('USERENV', 'TERMINAL') 客户端名称,
    SYS_CONTEXT ('USERENV', 'LANGUAGE') 客户端语言,
    SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,
    SYS_CONTEXT ('USERENV', 'INSTANCE') instance,
    SYS_CONTEXT ('USERENV', 'ENTRYID') entryid,
    SYS_CONTEXT ('USERENV', 'ISDBA') isdba,
    SYS_CONTEXT ('USERENV', 'NLS_TERRITORY') 地区,
    SYS_CONTEXT ('USERENV', 'NLS_CURRENCY') 货币,
    SYS_CONTEXT ('USERENV', 'NLS_CALENDAR') nls_calendar,
    SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT') 时间格式,
    SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE') 时间语言,
    SYS_CONTEXT ('USERENV', 'NLS_SORT') nls_sort,
    SYS_CONTEXT ('USERENV', 'CURRENT_USER') current_user,
    SYS_CONTEXT ('USERENV', 'CURRENT_USERID') current_userid,
    SYS_CONTEXT ('USERENV', 'SESSION_USER') session_user,
    SYS_CONTEXT ('USERENV', 'SESSION_USERID') session_userid,
    SYS_CONTEXT ('USERENV', 'PROXY_USER') proxy_user,
    SYS_CONTEXT ('USERENV', 'PROXY_USERID') proxy_userid,
    SYS_CONTEXT ('USERENV', 'DB_DOMAIN') db_domain,
    SYS_CONTEXT ('USERENV', 'DB_NAME') 数据库名称,
    SYS_CONTEXT ('USERENV', 'HOST') 客户端完成名称,
    SYS_CONTEXT ('USERENV', 'OS_USER') 客户端用户,
    SYS_CONTEXT ('USERENV', 'EXTERNAL_NAME') external_name,
    SYS_CONTEXT ('USERENV', 'IP_ADDRESS') 客户端IP地址,
    SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL') 网络协议,
    SYS_CONTEXT ('USERENV', 'BG_JOB_ID') bg_job_id,
    SYS_CONTEXT ('USERENV', 'FG_JOB_ID') fg_job_id,
    SYS_CONTEXT ('USERENV', 'AUTHENTICATION_TYPE') authentication_type,
    SYS_CONTEXT ('USERENV', 'AUTHENTICATION_DATA') authentication_data
    FROM DUAL；

• select user from dual;——查询当前数据库的登录用户名

• select userenv('language') from dual;——返回当前地区、语言和字符集

• select vsize('中') from dual;——获取内部表示的字节数