1\u3001\u9519\u8bef\u539f\u56e0\uff1a
\ndate\u7c7b\u578b\u4e0d\u80fd\u5305\u542b\u79d2\u4ee5\u540e\u7684\u7cbe\u5ea6\u3002
\n\u5982\u65e5\u671f\uff1a2010-01-01 20:02:20.0<\/p>\n
\u89e3\u51b3\u65b9\u6cd5\uff1a\u5c06\u65e5\u671f\u79d2\u4ee5\u540e\u7684\u7cbe\u5ea6\u53bb\u9664
\n\u5982\u65e5\u671f\uff1a2010-01-01 20:02:20<\/p>\n
2\u3001INSERT INTO TEST2 (C1, C2,c3) VALUES (${v1},${v2},to_date(${v3},’yyyy-mm-dd’));
\noracle\u91cc\u9762\u4e0d\u9700\u8981\u4ee5\u201c\uff1b\u201d\u7ed3\u5c3e\u3002<\/p>\n
3\u3001\u4fee\u6539\u6570\u636e\u5e93\u65e5\u671f\u683c\u5f0f\uff1a
\nalter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’<\/p>\n
\u8fd9\u4e2a\u547d\u4ee4\u7684\u9002\u7528\u8303\u56f4\uff0c\u662f\u4fee\u6539\u5f53\u524dSESSION\uff0c\u53e6\u5916\u8fd9\u4e0d\u80fd\u4fee\u6539\u7b2c\u4e09\u65b9\u5de5\u5177\u7684\u65e5\u671f\u683c\u5f0f\uff0c\u5982\uff1aPL\/SQL DEVELOPER\u9700\u8981\u5728Tools->Preferences->User Interface->Date\/Time\u4e0b\u4fee\u6539\u683c\u5f0f\u5373\u53ef\uff0c\u7b2c\u4e09\u65b9\u5de5\u5177\u7684\u65e5\u671f\u683c\u5f0f\u5728\u5de5\u5177\u5185\u90e8\u81ea\u5df1\u53bb\u8bbe\u7f6e\uff0c\u6211\u4f30\u8ba1\u4f60\u4e5f\u5e94\u8be5\u5728\u7b2c\u4e09\u65b9\u5de5\u5177\u4e0b\u624d\u4f1a\u770b\u5230\u8fd9\u6837\u7684\u6548\u679c\uff0c\u4e00\u822c\u9ed8\u8ba4\u60c5\u51b5\u4e0bORACLE\u9ed8\u8ba4\u7684\u65e5\u671f\u683c\u5f0f\u4e0d\u662f\u8fd9\u6837\u7684\u3002<\/p>\n
\u5728\u9ed8\u8ba4\u7684sqlplus\u6216\u8005sqlplusw\u4e0b\u6216\u8005\u7a0b\u5e8f\u7f16\u5199\u8fc7\u7a0b\u4e2d\uff0c\u6267\u884c\u8be5\u547d\u4ee4\u540e\uff0c\u5982\u679c\u662f\u540c\u4e00\u4e2asession\u662f\u80af\u5b9a\u4f1a\u4ea7\u751f\u4f60\u8981\u7684\u6548\u679c\u3002<\/p>\n
\u5982\u679c\u8981\u5c06\u6574\u4f53\u7684\u6570\u636e\u5e93\u7ea7\u522b\u9ed8\u8ba4\u683c\u5f0f\u6539\u6389\u9700\u8981\u4fee\u6539\u53c2\u6570\u6587\u4ef6\uff0c\u5e76\u91cd\u542f\u6570\u636e\u5e93\u624d\u53ef\u4ee5\uff0c\u4f46\u662f\u8fd9\u4e2a\u4e5f\u6539\u4e0d\u6389\u4f60\u7b2c\u4e09\u65b9\u5de5\u5177\u7684\u663e\u793a\u683c\u5f0f\uff0c\u7b2c\u4e09\u65b9\u5de5\u5177\u53ea\u80fd\u5728\u5de5\u5177\u5185\u90e8\u53bb\u6539\uff0c\u5b83\u53ea\u80fd\u6539\u6389\u9ed8\u8ba4\u7684\u65e5\u671f\u683c\u5f0f\uff0c\u7b2c\u4e09\u65b9\u5de5\u5177\u90fd\u662f\u8f6c\u6362\u540e\u7684\uff1b\u4f46\u662f\u5f88\u591a\u65f6\u5019\u5bb9\u6613\u88ab\u5de5\u5177\u6240\u5ffd\u60a0\uff0c\u7ed9\u4eba\u7684\u611f\u89c9\u5c31\u662f\u6570\u636e\u5e93\u5c31\u662f\u8fd9\u6837\u5b58\u50a8\u7684\uff0c\u5176\u5b9e\u5b58\u50a8\u7684\u53ea\u6709\u65e5\u671f\u683c\u5f0f\uff0c\u6ca1\u6709YYYYMM\u8fd9\u4e9b\u4e1c\u897f\uff0c\u8fd9\u4e9b\u4e1c\u897f\u90fd\u662f\u901a\u8fc7\u7b97\u6cd5\u8ba1\u7b97\u51fa\u6765\u7684\u7ed3\u679c\uff0c\u7136\u540e\u6839\u636e\u683c\u5f0f\u8981\u6c42\u8fdb\u884c\u5bf9\u5e94\u7684\u683c\u5f0f\u5316\u7ed9\u770b\u5230\u7684\u8fd9\u6837\u7684\u6548\u679c\u800c\u5df2\u3002<\/p>\n
4\u3001INSERT INTO TEST2 (C1, C2,c3) VALUES (‘1’,1,to_date(‘2010-12-27 00:00:00′,’yyyy-mm-dd’)) \u4e0d\u53ef\u4ee5
\nINSERT INTO TEST2 (C1, C2,c3) VALUES (1,1,to_date(‘2010-12-27 00:00:00′,’yyyy-mm-dd hh24:mi:ss’)) \u53ef\u4ee5
\nto_date\u662f\u7c7b\u578b\u8f6c\u6362\uff0c\u4e0d\u662f\u683c\u5f0f\u8f6c\u6362\uff0c\u5b57\u7b26\u4e32\u548c\u540e\u9762\u7684\u683c\u5f0f\u8981\u5339\u914d\uff1bto_char\u624d\u662f\u8f6c\u6210\u5bf9\u5e94\u683c\u5f0f\uff1b<\/p>\n
oracle\u65e5\u671f\u683c\u5f0f\u603b\u7ed3\u5728oracle\u4e2d\u5904\u7406\u65e5\u671f\u5927\u5168<\/p>\n
TO_DATE\u683c\u5f0f
\nDay:
\ndd number 12
\ndy abbreviated fri
\nday spelled out friday
\nddspth spelled out, ordinal twelfth
\nMonth:
\nmm number 03
\nmon abbreviated mar
\nmonth spelled out march
\nYear:
\nyy two digits 98
\nyyyy four digits 1998<\/p>\n
24\u5c0f\u65f6\u683c\u5f0f\u4e0b\u65f6\u95f4\u8303\u56f4\u4e3a\uff1a 0:00:00 – 23:59:59….
\n12\u5c0f\u65f6\u683c\u5f0f\u4e0b\u65f6\u95f4\u8303\u56f4\u4e3a\uff1a 1:00:00 – 12:59:59 ….
\n1.
\n\u65e5\u671f\u548c\u5b57\u7b26\u8f6c\u6362\u51fd\u6570\u7528\u6cd5\uff08to_date,to_char\uff09<\/p>\n
2.
\nselect to_char( to_date(222,’J’),’Jsp’) from dual<\/p>\n
\u663e\u793aTwo Hundred Twenty-Two<\/p>\n
3.
\n\u6c42\u67d0\u5929\u662f\u661f\u671f\u51e0
\nselect to_char(to_date(‘2002-08-26′,’yyyy-mm-dd’),’day’) from dual;
\n\u661f\u671f\u4e00
\nselect to_char(to_date(‘2002-08-26′,’yyyy-mm-dd’),’day’,’NLS_DATE_LANGUAGE = American’) from dual;
\nmonday
\n\u8bbe\u7f6e\u65e5\u671f\u8bed\u8a00
\nALTER SESSION SET NLS_DATE_LANGUAGE=’AMERICAN’;
\n\u4e5f\u53ef\u4ee5\u8fd9\u6837
\nTO_DATE (‘2002-08-26’, ‘YYYY-mm-dd’, ‘NLS_DATE_LANGUAGE = American’)<\/p>\n
4.
\n\u4e24\u4e2a\u65e5\u671f\u95f4\u7684\u5929\u6570
\nselect floor(sysdate – to_date(‘20020405′,’yyyymmdd’)) from dual;<\/p>\n
\u6ce8\u610f\u8981\u7528TO_DATE(null)<\/p>\n
6.
\na_date between to_date(‘20011201′,’yyyymmdd’) and to_date(‘20011231′,’yyyymmdd’)
\n\u90a3\u4e4812\u670831\u53f7\u4e2d\u534812\u70b9\u4e4b\u540e\u548c12\u67081\u53f7\u768412\u70b9\u4e4b\u524d\u662f\u4e0d\u5305\u542b\u5728\u8fd9\u4e2a\u8303\u56f4\u4e4b\u5185\u7684\u3002
\n\u6240\u4ee5\uff0c\u5f53\u65f6\u95f4\u9700\u8981\u7cbe\u786e\u7684\u65f6\u5019\uff0c\u89c9\u5f97to_char\u8fd8\u662f\u5fc5\u8981\u7684
\n7. \u65e5\u671f\u683c\u5f0f\u51b2\u7a81\u95ee\u9898
\n\u8f93\u5165\u7684\u683c\u5f0f\u8981\u770b\u4f60\u5b89\u88c5\u7684ORACLE\u5b57\u7b26\u96c6\u7684\u7c7b\u578b, \u6bd4\u5982: US7ASCII, date\u683c\u5f0f\u7684\u7c7b\u578b\u5c31\u662f: ’01-Jan-01′
\nalter system set NLS_DATE_LANGUAGE = American
\nalter session set NLS_DATE_LANGUAGE = American
\n\u6216\u8005\u5728to_date\u4e2d\u5199
\nselect to_char(to_date(‘2002-08-26′,’yyyy-mm-dd’),’day’,’NLS_DATE_LANGUAGE = American’) from dual;
\n\u6ce8\u610f\u6211\u8fd9\u53ea\u662f\u4e3e\u4e86NLS_DATE_LANGUAGE\uff0c\u5f53\u7136\u8fd8\u6709\u5f88\u591a\uff0c
\n\u53ef\u67e5\u770b
\nselect * from nls_session_parameters
\nselect * from V$NLS_PARAMETERS<\/p>\n
8.
\nselect count(*)
\nfrom ( select rownum-1 rnum
\nfrom all_objects
\nwhere rownum < = to_date(‘2002-02-28′,’yyyy-mm-dd’) – to_date(‘2002-
\n02-01′,’yyyy-mm-dd’)+1
\n)
\nwhere to_char( to_date(‘2002-02-01′,’yyyy-mm-dd’)+rnum-1, ‘D’ )
\nnot
\nin ( ‘1’, ‘7’ )<\/p>\n
\u67e5\u627e2002-02-28\u81f32002-02-01\u95f4\u9664\u661f\u671f\u4e00\u548c\u4e03\u7684\u5929\u6570
\n\u5728\u524d\u540e\u5206\u522b\u8c03\u7528DBMS_UTILITY.GET_TIME, \u8ba9\u540e\u5c06\u7ed3\u679c\u76f8\u51cf(\u5f97\u5230\u7684\u662f1\/100\u79d2, \u800c\u4e0d\u662f\u6beb\u79d2).<\/p>\n
9.
\nselect months_between(to_date(’01-31-1999′,’MM-DD-YYYY’),
\nto_date(’12-31-1998′,’MM-DD-YYYY’)) “MONTHS” FROM DUAL;
\n1<\/p>\n
select months_between(to_date(’02-01-1999′,’MM-DD-YYYY’),
\nto_date(’12-31-1998′,’MM-DD-YYYY’)) “MONTHS” FROM DUAL;<\/p>\n
1.03225806451613
\n10. Next_day\u7684\u7528\u6cd5
\nNext_day(date, day)<\/p>\n
Monday-Sunday, for format code DAY
\nMon-Sun, for format code DY
\n1-7, for format code D<\/p>\n
11
\nselect to_char(sysdate,’hh:mi:ss’) TIME from all_objects
\n\u6ce8\u610f\uff1a\u7b2c\u4e00\u6761\u8bb0\u5f55\u7684TIME \u4e0e\u6700\u540e\u4e00\u884c\u662f\u4e00\u6837\u7684
\n\u53ef\u4ee5\u5efa\u7acb\u4e00\u4e2a\u51fd\u6570\u6765\u5904\u7406\u8fd9\u4e2a\u95ee\u9898
\ncreate or replace function sys_date return date is
\nbegin
\nreturn sysdate;
\nend;<\/p>\n
select to_char(sys_date,’hh:mi:ss’) from all_objects;
\n12.
\n\u83b7\u5f97\u5c0f\u65f6\u6570<\/p>\n
SELECT EXTRACT(HOUR FROM TIMESTAMP ‘2001-02-16 2:38:40′) from offer
\nSQL> select sysdate ,to_char(sysdate,’hh’) from dual;<\/p>\n
SYSDATE TO_CHAR(SYSDATE,’HH’)<\/p>\n
2003-10-13 19:35:21 07<\/p>\n
SQL> select sysdate ,to_char(sysdate,’hh24′) from dual;<\/p>\n
SYSDATE TO_CHAR(SYSDATE,’HH24′)<\/p>\n
2003-10-13 19:35:21 19<\/p>\n
\u83b7\u53d6\u5e74\u6708\u65e5\u4e0e\u6b64\u7c7b\u4f3c
\n13.
\n\u5e74\u6708\u65e5\u7684\u5904\u7406
\nselect older_date,
\nnewer_date,
\nyears,
\nmonths,
\nabs(
\ntrunc(
\nnewer_date-
\nadd_months( older_date,years*12+months )
\n)
\n) days
\nfrom ( select
\ntrunc(months_between( newer_date, older_date )\/12) YEARS,
\nmod(trunc(months_between( newer_date, older_date )),
\n12 ) MONTHS,
\nnewer_date,
\nolder_date
\nfrom ( select hiredate older_date,
\nadd_months(hiredate,rownum)+rownum newer_date
\nfrom emp )
\n)<\/p>\n
14.
\n\u5904\u7406\u6708\u4efd\u5929\u6570\u4e0d\u5b9a\u7684\u529e\u6cd5
\nselect to_char(add_months(last_day(sysdate) +1, -2), ‘yyyymmdd’),last_day(sysdate) from dual<\/p>\n
16.
\n\u627e\u51fa\u4eca\u5e74\u7684\u5929\u6570
\nselect add_months(trunc(sysdate,’year’), 12) – trunc(sysdate,’year’) from dual<\/p>\n
\u95f0\u5e74\u7684\u5904\u7406\u65b9\u6cd5
\nto_char( last_day( to_date(’02’ || :year,’mmyyyy’) ), ‘dd’ )
\n\u5982\u679c\u662f28\u5c31\u4e0d\u662f\u95f0\u5e74<\/p>\n
17.
\nyyyy\u4e0errrr\u7684\u533a\u522b
\n‘YYYY99 TO_C<\/p>\n
yyyy 99 0099
\nrrrr 99 1999
\nyyyy 01 0001
\nrrrr 01 2001<\/p>\n
18.\u4e0d\u540c\u65f6\u533a\u7684\u5904\u7406
\nselect to_char( NEW_TIME( sysdate, ‘GMT’,’EST’), ‘dd\/mm\/yyyy hh:mi:ss’) ,sysdate
\nfrom dual;<\/p>\n
19.
\n5\u79d2\u949f\u4e00\u4e2a\u95f4\u9694
\nSelect TO_DATE(FLOOR(TO_CHAR(sysdate,’SSSSS’)\/300) * 300,’SSSSS’) ,TO_CHAR(sysdate,’SSSSS’)
\nfrom dual<\/p>\n
2002-11-1 9:55:00 35786
\nSSSSS\u8868\u793a5\u4f4d\u79d2\u6570<\/p>\n
20.
\n\u4e00\u5e74\u7684\u7b2c\u51e0\u5929
\nselect TO_CHAR(SYSDATE,’DDD’),sysdate from dual
\n310 2002-11-6 10:03:51<\/p>\n
21.\u8ba1\u7b97\u5c0f\u65f6,\u5206,\u79d2,\u6beb\u79d2 select * from tabname \/\/ oracle\u4e2d\u6709\u5f88\u591a\u5173\u4e8e\u65e5\u671f\u7684\u51fd\u6570<\/p>\n \u5728oracle\u4e2d\u6709\u5f88\u591a\u5173\u4e8e\u65e5\u671f\u7684\u51fd\u6570\uff0c\u5982\uff1a 13-11\u6708-04<\/p>\n SQL> select add_months(sysdate,112) “Last Year” from dual;<\/p>\n 13-3\u6708 -13<\/p>\n SQL><\/p>\n 2\u3001current_date()\u8fd4\u56de\u5f53\u524d\u4f1a\u653e\u65f6\u533a\u4e2d\u7684\u5f53\u524d\u65e5\u671f SESSIONTIMEZONE CURRENT_DA<\/p>\n +08:00 13-11\u6708-03<\/p>\n SQL> alter session set time_zone=’-11:00′ \u4f1a\u8bdd\u5df2\u66f4\u6539\u3002<\/p>\n SQL> select sessiontimezone,current_timestamp from dual;<\/p>\n SESSIONTIMEZONE CURRENT_TIMESTAMP<\/p>\n -11:00 12-11\u6708-03 04.59.13.668000 \u4e0b\u5348 -11: SQL><\/p>\n 3\u3001current_timestamp()\u4ee5timestamp with time zone\u6570\u636e\u7c7b\u578b\u8fd4\u56de\u5f53\u524d\u4f1a\u653e\u65f6\u533a\u4e2d\u7684\u5f53\u524d\u65e5\u671f SESSIONTIMEZONE CURRENT_TIMESTAMP<\/p>\n +08:00 13-11\u6708-03 11.56.28.160000 \u4e0a\u5348 +08: SQL> alter session set time_zone=’-11:00′ \u4f1a\u8bdd\u5df2\u66f4\u6539\u3002<\/p>\n SQL> select sessiontimezone,current_timestamp from dual;<\/p>\n SESSIONTIMEZONE CURRENT_TIMESTAMP<\/p>\n -11:00 12-11\u6708-03 04.58.00.243000 \u4e0b\u5348 -11: SQL><\/p>\n 4\u3001dbtimezone()\u8fd4\u56de\u65f6\u533a -07:00<\/p>\n SQL><\/p>\n 5\u3001extract()\u627e\u51fa\u65e5\u671f\u6216\u95f4\u9694\u503c\u7684\u5b57\u6bb5\u503c 11<\/p>\n SQL> select extract(year from add_months(sysdate,36)) “3 Years Out” from dual;<\/p>\n 2006<\/p>\n SQL><\/p>\n 6\u3001last_day()\u8fd4\u56de\u5305\u542b\u4e86\u65e5\u671f\u53c2\u6570\u7684\u6708\u4efd\u7684\u6700\u540e\u4e00\u5929\u7684\u65e5\u671f 29-2\u6708 -00<\/p>\n SQL> select last_day(sysdate) “Last day of this month” from dual;<\/p>\n 30-11\u6708-03<\/p>\n SQL><\/p>\n 7\u3001localtimestamp()\u8fd4\u56de\u4f1a\u8bdd\u4e2d\u7684\u65e5\u671f\u548c\u65f6\u95f4 13-11\u6708-03 12.09.15.433000 SQL> select localtimestamp,current_timestamp from dual;<\/p>\n LOCALTIMESTAMP CURRENT_TIMESTAMP<\/p>\n 13-11\u6708-03 12.09.31.006000 13-11\u6708-03 12.09.31.006000 \u4e0b\u5348 +08: SQL> alter session set time_zone=’-11:00′;<\/p>\n \u4f1a\u8bdd\u5df2\u66f4\u6539\u3002<\/p>\n SQL> select localtimestamp,to_char(sysdate,’DD-MM-YYYY HH:MI:SS AM’) “SYSDATE” from dual;<\/p>\n LOCALTIMESTAMP SYSDATE<\/p>\n 12-11\u6708-03 05.11.31.259000 13-11-2003 12:11:31 \u4e0b\u5348 SQL><\/p>\n 8\u3001months_between()\u5224\u65ad\u4e24\u4e2a\u65e5\u671f\u4e4b\u95f4\u7684\u6708\u4efd\u6570\u91cf 389.855143<\/p>\n SQL> select months_between(sysdate,date’2001-01-01′) from dual;<\/p>\n 34.4035409<\/p>\n SQL><\/p>\n 9\u3001next_day()\u7ed9\u5b9a\u4e00\u4e2a\u65e5\u671f\u503c\uff0c\u8fd4\u56de\u7531\u7b2c\u4e8c\u4e2a\u53c2\u6570\u6307\u51fa\u7684\u65e5\u5b50\u7b2c\u4e00\u6b21\u51fa\u73b0\u5728\u7684\u65e5\u671f\u503c\uff08\u5e94\u8fd4\u56de\u76f8\u5e94\u65e5\u5b50\u7684\u540d\u79f0\u5b57\u7b26\u4e32\uff09<\/p>\n \u8207\u5468\u76f8\u95dc\u65e5\u671f\u51fd\u6578<\/p>\n 1.\u67e5\u8be2\u67d0\u5468\u7684\u7b2c\u4e00\u5929 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<\/p>\n select min(v_date) from 2.\u67e5\u8be2\u67d0\u5468\u7684\u6700\u540e\u4e00\u5929 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<\/p>\n select max(v_date) from 3.\u67e5\u8be2\u67d0\u5468\u7684\u65e5\u671f select to_date(substr(‘2004-33’,1,4)-1|| select to_date(substr(‘2004-33′,1,4)+1||’001’+rownum-1,’yyyyddd’) min_date oracle\u4e2d\u65f6\u95f4\u8fd0\u7b97<\/p>\n \u8bba\u575b\u4e2d\u5e38\u5e38\u770b\u5230\u6709\u5bf9oracle\u4e2d\u65f6\u95f4\u8fd0\u7b97\u63d0\u95ee\u7684\u95ee\u9898\uff0c\u4eca\u5929\u6709\u65f6\u95f4\uff0c\u770b\u4e86\u770b\u4ee5\u524d\u5404\u4f4d\u5144\u5f1f\u7684\u8d34\u5b50\uff0c\u6574\u7406\u4e86\u4e00\u4e0b\uff0c\u5e76\u4f5c\u4e86\u4e2a\u793a\u4f8b\uff0c\u5e0c\u671b\u4f1a\u5bf9\u5927\u5bb6\u6709\u5e2e\u52a9\u3002 \u5185\u5bb9\u5982\u4e0b\uff1a SQL> alter session set nls_date_format=’yyyy-mm-dd hh:mi:ss’;<\/p>\n \u4f1a\u8bdd\u5df2\u66f4\u6539\u3002<\/p>\n SQL> set serverout on PL\/SQL \u8fc7\u7a0b\u5df2\u6210\u529f\u5b8c\u6210\u3002<\/p>\n","protected":false},"excerpt":{"rendered":" 1\u3001\u9519\u8bef\u539f\u56e0\uff1a date\u7c7b\u578b\u4e0d\u80fd\u5305\u542b\u79d2\u4ee5\u540e\u7684\u7cbe\u5ea6\u3002 \u5982\u65e5\u671f\uff1a2010-01-01 20:02:20.0 \u89e3\u51b3\u65b9 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"aside","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[],"class_list":["post-44","post","type-post","status-publish","format-aside","hentry","category-tech","post_format-post-format-aside"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.espandy.com\/index.php?rest_route=\/wp\/v2\/posts\/44","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.espandy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.espandy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.espandy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.espandy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=44"}],"version-history":[{"count":3,"href":"https:\/\/www.espandy.com\/index.php?rest_route=\/wp\/v2\/posts\/44\/revisions"}],"predecessor-version":[{"id":47,"href":"https:\/\/www.espandy.com\/index.php?rest_route=\/wp\/v2\/posts\/44\/revisions\/47"}],"wp:attachment":[{"href":"https:\/\/www.espandy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=44"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.espandy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=44"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.espandy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nselect
\nDays,
\nA,
\nTRUNC(A24) Hours,
\nTRUNC(A<\/em>2460 – 60<\/em>TRUNC(A24)) Minutes,
\nTRUNC(A<\/em>2460<\/em>60 – 60TRUNC(A<\/em>2460)) Seconds,
\nTRUNC(A<\/em>2460<\/em>60100 – 100<\/em>TRUNC(A24<\/em>60*60)) mSeconds
\nfrom
\n(
\nselect
\ntrunc(sysdate) Days,
\nsysdate – trunc(sysdate) A
\nfrom dual
\n)<\/p>\n
\norder by decode(mode,’FIFO’,1,-1)*to_char(rq,’yyyymmddhh24miss’);<\/p>\n
\nfloor((date2-date1) \/365) \u4f5c\u4e3a\u5e74
\nfloor((date2-date1, 365) \/30) \u4f5c\u4e3a\u6708
\nmod(mod(date2-date1, 365), 30)\u4f5c\u4e3a\u65e5.
\n23.next_day\u51fd\u6570
\nnext_day(sysdate,6)\u662f\u4ece\u5f53\u524d\u5f00\u59cb\u4e0b\u4e00\u4e2a\u661f\u671f\u4e94\u3002\u540e\u9762\u7684\u6570\u5b57\u662f\u4ece\u661f\u671f\u65e5\u5f00\u59cb\u7b97\u8d77\u3002
\n1 2 3 4 5 6 7
\n\u65e5 \u4e00 \u4e8c \u4e09 \u56db \u4e94 \u516d<\/p>\n
\n1\u3001add_months()\u7528\u4e8e\u4ece\u4e00\u4e2a\u65e5\u671f\u503c\u589e\u52a0\u6216\u51cf\u5c11\u4e00\u4e9b\u6708\u4efd
\ndate_value:=add_months(date_value,number_of_months)
\n\u4f8b\uff1a
\nSQL> select add_months(sysdate,12) “Next Year” from dual;<\/p>\nNext Year<\/h2>\n
Last Year<\/h2>\n
\ndate_value:=current_date
\nSQL> column sessiontimezone for a15
\nSQL> select sessiontimezone,current_date from dual;<\/p>\n
\n
\n2 \/<\/p>\n
\n
\n00<\/p>\n
\ntimestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
\nSQL> column sessiontimezone for a15
\nSQL> column current_timestamp format a36
\nSQL> select sessiontimezone,current_timestamp from dual;<\/p>\n
\n
\n00<\/p>\n
\n2 \/<\/p>\n
\n
\n00<\/p>\n
\nvarchar_value:=dbtimezone
\nSQL> select dbtimezone from dual;<\/p>\nDBTIME<\/h2>\n
\ndate_value:=extract(date_field from [datetime_value|interval_value])
\nSQL> select extract(month from sysdate) “This Month” from dual;<\/p>\nThis Month<\/h2>\n
3 Years Out<\/h2>\n
\ndate_value:=last_day(date_value)
\nSQL> select last_day(date’2000-02-01′) “Leap Yr?” from dual;<\/p>\nLeap Yr?<\/h2>\n
Last day o<\/h2>\n
\ntimestamp_value:=localtimestamp
\nSQL> column localtimestamp format a28
\nSQL> select localtimestamp from dual;<\/p>\nLOCALTIMESTAMP<\/h2>\n
\n\u4e0b\u5348<\/p>\n
\n
\n\u4e0b\u5348 00<\/p>\n
\n
\n\u4e0b\u5348<\/p>\n
\nnumber_value:=months_between(date_value,date_value)
\nSQL> select months_between(sysdate,date’1971-05-18′) from dual;<\/p>\nMONTHS_BETWEEN(SYSDATE,DATE’1971-05-18′)<\/h2>\n
MONTHS_BETWEEN(SYSDATE,DATE’2001-01-01′)<\/h2>\n
\nselect trunc(decode(ww, 53, to_date(yy || ‘3112’, ‘yyyyddmm’), to_date(yy || ‘-‘ || to_char(ww * 7), ‘yyyy-ddd’)), ‘d’) last_day
\nfrom (select substr(‘2004-32’, 1, 4) yy, to_number(substr(‘2004-32’, 6)) ww
\nfrom dual)<\/p>\n
\n(select (to_date(‘200201′,’yyyymm’) + rownum) v_date
\nfrom all_tables
\nwhere rownum < 370)
\nwhere to_char(v_date,’yyyy-iw’) = ‘2002-49’<\/p>\n
\nselect trunc(decode(ww, 53, to_date(yy || ‘3112’, ‘yyyyddmm’), to_date(yy || ‘-‘ || to_char(ww * 7), ‘yyyy-ddd’)), ‘d’) – 6 first_day
\nfrom (select substr(‘2004-33’, 1, 4) yy, to_number(substr(‘2004-33’, 6)) ww
\nfrom dual)<\/p>\n
\n(select (to_date(‘200408′,’yyyymm’) + rownum) v_date
\nfrom all_tables
\nwhere rownum < 370)
\nwhere to_char(v_date,’yyyy-iw’) = ‘2004-33’<\/p>\n
\nselect min_date, to_char(min_date,’day’) day from
\n(select to_date(substr(‘2004-33′,1,4)||’001’+rownum-1,’yyyyddd’) min_date
\nfrom all_tables
\nwhere rownum <= decode(mod(to_number(substr(‘2004-33’,1,4)),4),0,366,365)
\nunion<\/p>\n
\ndecode(mod(to_number(substr(‘2004-33′,1,4))-1,4),0,359,358)+rownum,’yyyyddd’) min_date
\nfrom all_tables
\nwhere rownum <= 7
\nunion<\/p>\n
\nfrom all_tables
\nwhere rownum <= 7
\n)
\nwhere to_char(min_date,’yyyy-iw’) =’2004-33′<\/p>\n
\n\u9996\u5148\u611f\u8c22ern\u3001eric.li\u53ca\u5404\u7248\u4e3b\u8fd8\u6709\u70ed\u5fc3\u7684\u5144\u5f1f\u4eec<\/p>\n
\n1\u3001oracle\u652f\u6301\u5bf9\u65e5\u671f\u8fdb\u884c\u8fd0\u7b97
\n2\u3001\u65e5\u671f\u8fd0\u7b97\u65f6\u662f\u4ee5\u5929\u4e3a\u5355\u4f4d\u8fdb\u884c\u7684
\n3\u3001\u5f53\u9700\u8981\u4ee5\u5206\u79d2\u7b49\u66f4\u5c0f\u7684\u5355\u4f4d\u7b97\u503c\u65f6\uff0c\u6309\u65f6\u95f4\u8fdb\u5236\u8fdb\u884c\u8f6c\u6362\u5373\u53ef
\n4\u3001\u8fdb\u884c\u65f6\u95f4\u8fdb\u5236\u8f6c\u6362\u65f6\u6ce8\u610f\u52a0\u62ec\u53f7\uff08\u89c1\u793a\u4f8b\u4e2d\u7ea2\u8272\u62ec\u53f7\uff09\uff0c\u5426\u5219\u4f1a\u51fa\u95ee\u9898<\/p>\n
\nSQL> declare
\n2 DateValue date;
\n3 begin
\n4 select sysdate into DateValue from dual;
\n5 dbms_output.put_line(‘\u6e90\u65f6\u95f4:’||to_char(DateValue));
\n6 dbms_output.put_line(‘\u6e90\u65f6\u95f4\u51cf1\u5929:’||to_char(DateValue-1));
\n7 dbms_output.put_line(‘\u6e90\u65f6\u95f4\u51cf1\u59291\u5c0f\u65f6:’||to_char(DateValue-1-1\/24));
\n8 dbms_output.put_line(‘\u6e90\u65f6\u95f4\u51cf1\u59291\u5c0f\u65f61\u5206:’||to_char(DateValue-1-1\/24-1\/(2460)));
\n9 dbms_output.put_line(‘\u6e90\u65f6\u95f4\u51cf1\u59291\u5c0f\u65f61\u52061\u79d2:’||to_char(DateValue-1-1\/24-1\/(24<\/em>60)-1\/(2460<\/em>6
\n0)));
\n10 end;
\n11 \/
\n\u6e90\u65f6\u95f4:2003-12-29 11:53:41
\n\u6e90\u65f6\u95f4\u51cf1\u5929:2003-12-28 11:53:41
\n\u6e90\u65f6\u95f4\u51cf1\u59291\u5c0f\u65f6:2003-12-28 10:53:41
\n\u6e90\u65f6\u95f4\u51cf1\u59291\u5c0f\u65f61\u5206:2003-12-28 10:52:41
\n\u6e90\u65f6\u95f4\u51cf1\u59291\u5c0f\u65f61\u52061\u79d2:2003-12-28 10:52:40<\/p>\n