1.查询table 所有列名
用Oracle的数据字典即可:
SELECT t.COLUMN_NAME FROM USER_TAB_COLUMNS t where t.TABLE_NAME='TESTSUPPORT';
2. 相差一个月函数
SELECT to_char(:v_txn_date_fm + interval '1' month,'yyyy/mm/dd') from dual;
转:
INTERVAL
语法:
INTERVAL '{ integer | integer time_expr | time_expr }'
{ { DAY | HOUR | MINUTE } [ ( leading_precision ) ]
| SECOND [ ( leading_precision [, fractional_seconds_precision ] ) ] }
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]
leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
INTERVAL '400 5' DAY(3) TO HOUR
表示: 400天5小时, 400为3为精度,所以"DAY(3)", 注意默认值为2
INTERVAL '30.12345' SECOND(2,4)
表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入
select date '2010-01-01' + interval '21' year from dual
select date '2010-01-01' + interval '123-2' year(4) to month from dual
select date '2010-01-01' + interval '100' month from dual
select date '2010-01-01' + interval '100' day(2) from dual
select date '2010-01-01' + interval '1 2' day to hour from dual
select date '2010-01-01' + interval '1 2:3' day to minute from dual
select date '2010-01-01' + interval '1 2:3:4' day to second from dual
select date '2010-01-01' + interval '1' hour from dual
select date '2010-01-01' + interval '1:2' hour to minute from dual
select date '2010-01-01' + interval '1:2:3' hour to second from dual
select date '2010-01-01' + interval '1:2' minute to second from dual
3. 月与月之间相差几个月
select floor(months_between(to_date(:P_TXN_DATE_TO,'yyyy/mm/dd hh24:mi:ss'),to_date(:P_TXN_DATE_FM,'yyyy/mm/dd hh24:mi:ss')))
from dual;
转:
oracle 日期常用函數:
(ADD_MONTHS,LAST_DAY,NEXT_DAY,MONTHS_BETWEEN,NEW_TIME,ROUND,TRUNC)
日期運算函數
2
3 ADD_MONTHS(d,n)
4 --時間點d再加上n個月
5
6 ex.
7 select sysdate, add_months(sysdate,2) aa from dual;
8
9 SYSDATE AA
10 ---------- ----------
11 21-SEP-07 21-NOV-07
12
13 LAST_DAY(d)
14 --時間點d當月份最後一天
15
16 ex.
17 select sysdate, LAST_DAY(sysdate) LAST_DAY from dual;
18
19 SYSDATE LAST_DAY
20 ---------- ---------
21 21-SEP-07 30-SEP-07
22
23 NEXT_DAY(d,number)
24 --◎ 時間點d開始,下一個星期幾的日期
25 --◎ 星期日 = 1 星期一 = 2 星期二 = 3
26 -- 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7
27
28 ex.
29 select sysdate, NEXT_DAY(sysdate,2) aa from dual;
30
31 SYSDATE AA
32 ---------- ----------
33 21-SEP-07 24-SEP-07
34
35 MONTHS_BETWEEN(d1,d2)
36 --計算d1與d2相隔的月數
37
38 ex.
39 select trunc(MONTHS_BETWEEN(to_date('20071101','yyyymmdd'),
40 to_date('20070820','yyyymmdd'))) aa
41 from dual;
42
43 AA
44 ----------
45 2
46
47 NEW_TIME(d,c1,c2)
48 --轉換新時區
49
50 ex.
51 select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') 台北,
52 to_char(NEW_TIME(sysdate,'EST','GMT'),'YYYY/MM/DD HH24:MI:SS') 格林威治
53 from dual;
54
55 台北 格林威治
56 -------------------- -------------------
57 2007/09/21 14:36:53 2007/09/21 19:36:53
58
59 ROUND(d[,fmt])
60 --◎ 對日期作四捨五入運算
61 --◎ 月的四捨五入以每月的15號為基準
62 --◎ 年的四捨五入以每年6月為基準
63
64 ex.
65 select sysdate, ROUND(sysdate,'year') aa from dual;
66
67 SYSDATE AA
68 ---------- ----------
69 21-SEP-07 01-JAN-08
70
71 select sysdate, ROUND(sysdate,'month') aa from dual;
72
73 SYSDATE AA
74 ---------- ----------
75 21-SEP-07 01-OCT-07
76
77 TRUNC(d[,fmt])
78 --對日期作擷取運算
79
80 ex.
81 select sysdate, TRUNC(sysdate,'year') aa from dual;
82
83 SYSDATE AA
84 ---------- ----------
85 21-SEP-07 01-JAN-07
86
87 select sysdate, TRUNC(sysdate,'month') aa from dual;
88
89 SYSDATE AA
90 ---------- ----------
91 21-SEP-07 01-SEP-07