ORACLE日期函数.docx

1、ORACLE日期函数oracle trunc(sysdate,Q) - trunc(sysdate,D) -() Oracle - select * from nls_database_parameters; : NLS_DATE_FORMAT. PARAMETER VALUE - - NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK NLS_CALENDAR GREGORI

2、AN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_NCHAR_CHARAC

3、TERSET ZHS16GBK NLS_RDBMS_VERSION 8.1.7.0.0 V$NLS_PARAMETERS, select * from V$NLS_PARAMETERS; . to_date , SQLselect to_date(2004-11-12 12-07-32,yyyy-mm-dd hh24-mi-ss) value from dual; VALUE - 2004.11.12 12:07:32 SQLselect to_date(20041015) value from dual; VALUE - 2004.10.15 00:00:00 SQLselect to_da

4、te(20041315) value from dual; ERROR 1 : ORA-01861: sysdate SQLselect sysdate value from dual; VALUE - 2003.11.23 17:09:01 last_day SQLselect last_day(sysdate) value from dual; VALUE - 2003.11.30 17:08:17 add_months(d,n) dn SQLselect add_months(sysdate,2) value from dual; VALUE - 2005.01.23 17:10:21

5、next_day(d,day) d,() SQLselect next_day(sysdate,1) value from dual; VALUE - 2004.11.28 17:38:55 oraclesqlOracle 1Sysdate SQL Select sysdate from dual; SYSDATE - 21-6 -05 2Last_day SQL Select last_day(sysdate) from dual; LAST_DAY(S - 30-6 -05 3Add_months(d,n) dn date_value:=add_months(date_value,numb

6、er_of_months) SQL Select add_months(sysdate,2) from dual; ADD_MONTHS - 21-8 -05 4Months_between(f,s) fs SQL select months_between(sysdate,to_date(2005-11-12,yyyy-mm-dd)from dual; MONTHS_BETWEEN(SYSDATE,TO_DATE(2005-11-12,YYYY-MM-DD) - -4.6966741 5NEXT_DAY(d, day_of_week) day_of_weekdday_of_week SQL

7、SELECT next_day(to_date(20050620,YYYYMMDD),1) FROM dual; NEXT_DAY(T - 26-6 -05 6current_date() date_value:=current_date SQL column sessiontimezone for a15 SQL select sessiontimezone,current_date from dual; SESSIONTIMEZONE CURRENT_DA - - +08:00 13-11-03 SQL alter session set time_zone=-11:00 2 / SQL

8、select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP - - -11:00 12-11-03 04.59.13.668000 -11:00 7current_timestamp()timestamp with time zone SQL select current_timestamp from dual; CURRENT_TIMESTAMP - 21-6 -05 10.13.08.220589 +08:00 8dbtimezone() SQL select dbtimezon

9、e from dual; DBTIME - -08:00 9extract() date_value:=extract(date_field from datetime_value|interval_value) SQL select extract(month from sysdate) This Month from dual; This Month - 6 SQL select extract(year from add_months(sysdate,36) Years from dual; Years - 2008 10localtimestamp() SQL select local

10、timestamp from dual; LOCALTIMESTAMP - 21-6 -05 10.18.15.855652 YYYYYY Select to_char(sysdate,YYY) from dual 0022002 SYEARYEAR SYEAR Select to_char(sysdate,SYEAR) from dual -1112111 2 Q 13 Select to_char(sysdate,Q) from dual 2 MM Select to_char(sysdate,MM) from dual 1212 RM Select to_char(sysdate,RM)

11、 from dual IV4 Month 9 Select to_char(sysdate,Month) from dual May65 WW Select to_char(sysdate,WW) from dual 24200261324 W Select to_char(sysdate,W) from dual 20021011 DDD , 1100121032 Select to_char(sysdate,DDD) from dual 363 20021 22 9363 DD Select to_char(sysdate,DD) from dual 04 1044 D Select to

12、_char(sysdate,D) from dual 5 2002314 DY Select to_char(sysdate,DY) from dual SUN 2002324 HHHH12 12 Select to_char(sysdate,HH) from dual 02 2802 HH24 24 Select to_char(sysdate,HH24) from dual 14 20814 MI (059) Select to_char(sysdate,MI) from dual 17417 SS (059) Select to_char(sysdate,SS) from dual 22

13、 11322 MM(MI)MM 1 SQL select to_char(add_months(last_day(sysdate),-1),yyyy-MM-dd) LastDay from dual; LASTDAY - 2005-05-31 2 SQL select to_char(add_months(sysdate,-1),yyyy-MM-dd) PreToday from dual; PRETODAY - 2005-05-21 3. SQL select to_char(add_months(last_day(sysdate)+1,-2),yyyy-MM-dd) firstDay fr

14、om dual; FIRSTDAY - 2005-05-01 4. SQL select to_char(sysdate,ww) from dual group by to_char(sysdate,ww); TO - 25 5 SQL select to_char(sysdate,mm) from dual group by to_char(sysdate,mm); TO - 06 6 SQL select to_char(sysdate,q) from dual group by to_char(sysdate,q); T - 2 7 SQL select to_char(sysdate,

15、yyyy) from dual group by to_char(sysdate,yyyy); TO_C - 2005 8. select to_char(t.d,YY-MM-DD) from ( select trunc(sysdate, MM)+rownum-1 as d from dba_objects where rownum alter session set nls_date_format=yyyy-mm-dd hh:mi:ss; SQL set serverout on SQL declare 2 DateValue date; 3 begin 4 select sysdate

16、into DateValue from dual; 5 dbms_output.put_line(:|to_char(DateValue); 6 dbms_output.put_line(1:|to_char(DateValue-1); 7 dbms_output.put_line(11:|to_char(DateValue-1-1/24); 8 dbms_output.put_line(111:|to_char(DateValue-1-1/24-1/(24*60); 9 dbms_output.put_line(1111:|to_char(DateValue-1-1/24-1/(24*60)

17、-1/(24*60*60); 10 end; 11 / :2003-12-29 11:53:41 1:2003-12-28 11:53:41 11:2003-12-28 10:53:41 111:2003-12-28 10:52:41 1111:2003-12-28 10:52:40 PL/SQL Oracle - Add_Times - d1NewTime - NewTime - 2004-12-07 - 1.0 - Kevin create or replace function Add_Times(d1 in date,NewTime in date) return date is hh

18、 number; mm number; ss number; hours number; dResult date; begin - select to_number(to_char(NewTime,HH24) into hh from dual; select to_number(to_char(NewTime,MI) into mm from dual; select to_number(to_char(NewTime,SS) into ss from dual; - NewTime hours := (hh + (mm / 60) + (ss / 3600)/ 24; - select

19、d1 + hours into dResult from dual; return(dResult); end Add_Times; - - select Add_Times(sysdate,to_date(2004-12-06 03:23:00,YYYY-MM-DD HH24:MI:SS) from dual Oracle9i Oracle DATAOracle12 round(to_number(end-date-start_date)- round(to_number(end-date-start_date)*24)- round(to_number(end-date-start_date)*1440)- SQ