Oracle数据库日期处理(三).docxVIP

last_day()返回包含了日期参数的月份的最后一天的日期 date_value:=last_day(date_value) SQL select last_day(date2000-02-01) Leap Yr? from dual; Leap Yr? 29-2 月 -00 SQL select last_day(sysdate) Last day of this month from dual; Last day o 30-11 月-03 SQL localtimestamp()返回会话中的日期和时间 timestamp_value:=localtimestamp SQL column localtimestamp format a28 SQL select localtimestamp from dual; LOCALTIMESTAMP 13-11 月-03 12.09.15.433000 下午 SQL select localtimestamp,current_timestamp from dual; LOCALTIMESTAMP CURRENT_TIMESTAMP 13-11 月-03 12.09.31.006000 13-11 月-03 12.09.31.006000 下午 +08: 下午 00 SQL alter session set time_zone=-11:00; 会话已更改。 SQL select localtimestamp,to_char(sysdate,DD-MM-YYYY HH:MI:SS AM) SYSDATE from dual; LOCALTIMESTAMP SYSDATE 12-11 月-03 05.11.31.259000 13-11-2003 12:11:31 下午 下午 SQL months_between() 判 断 两 个 日 期 之 间 的 月 份 数 量 number_value:=months_between(date_value,date_value) SQL select months_between(sysdate,date1971-05-18) from dual; MONTHS_BETWEEN(SYSDATE,DATE1971-05-18) 389.855143 SQL select months_between(sysdate,date2001-01-01) from dual; MONTHS_BETWEEN(SYSDATE,DATE2001-01-01) 34.4035409 SQL next_day()给定一个日期值，返回由第二个参数指出的日子第一次出现在的日期值(应返回相应日子的名称字符串) 查询某周的第一天 select trunc(decode(ww, 53, to_date(yy || 3112, yyyyddmm), to_date(yy || - || to_char(ww * 7), yyyy-ddd)), d) last_day from (select substr(2004-32, 1, 4) yy, to_number(substr(2004-32, 6)) ww from dual) select trunc(to_date(substr(2003-01,1,5)||to_char((to_number(substr(2003-01,6)))*7),yyyy-ddd),d )-6 first_day from dual select min(v_date) from (select (to_date(200201,yyyymm) + rownum) v_date from all_tables where rownum 370) where to_char(v_date,yyyy-iw) = 2002-49 查询某周的最后一天 select trunc(decode(ww, 53, to_date(yy || 3112, yyyyddmm), to_date(yy || - || to_char(ww * 7), yyyy-ddd)), d) - 6 first_day from (select substr(2004-33, 1, 4) yy, to_number(substr(2004-33, 6)) ww from dual) select trunc(to_date(substr(2003-01,1,5)||to_char((to_number(substr(2003-01,6)))*7),yyyy-ddd),d) last_day from dual select max(v_date) from (select (t