Oracle TO_DATE 日期格式大全
Oracle中TO_DATE格式2009-04-14 10:53TO_DATE格式(以时
间:2007-11-02 13:45:25为例)
Year:
yy two digits 两位年显示值:07
yyy three digits 三位年显示值:007
yyyy four digits 四位年显示值:2007
Month:
mm number 两位月显示值:11
mon abbreviated 字符集表示显示值:11月,若是英文版,显示nov
month spelled out 字符集表示显示值:11月,若是英文版,显示november
Day:
dd number 当月第几天显示值:02
ddd number 当年第几天显示值:02
dy abbreviated 当周第几天简写显示值:星期五,若是英文版,显示fri
day spelled out 当周第几天全写显示值:星期五,若是英文版,显示friday
ddspth spelled out, ordinal twelfth
Hour:
hh two digits 12小时进制显示值:01
hh24 two digits 24小时进制显示值:13
Minute:
mi two digits 60进制显示值:45
Second:
ss two digits 60进制显示值:25
其它
Q digit 季度显示值:4 WW digit 当年第几周显示值:44 W digit 当月第几周显示值:1
24小时格式下时间范围为： 0:00:00 - 23:59:59....
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
1. 日期和字符转换函数用法（to_date,to_char）
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年
select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月
select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日
select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时
select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分
select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒
select to_date('2004-05-07 13:23:44','yyyy-mm-dd
hh24:mi:ss') from dual//
2.
select to_char( to_date(222,'J'),'Jsp') from dual
显示Two Hundred Twenty-Two
3.求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
select
to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4. 两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. 时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意要用TO_DATE(null)
6.月份差
a_date between to_date('20011201','yyyymmdd') and
to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select
to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') -
to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1,
'D' )
not in ( '1', '7' )
查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).
9. 查找月份
select
months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998 ','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1
select
months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998 ','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613
10. Next_day的用法
Next_day(date, day)
Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D
11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end;
select to_char(sys_date,'hh:mi:ss') from all_objects;
12.获得小时数
extract()找出日期或间隔值的字段值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
13.年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,
newer_date,
older_date
from (
select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp
)
)
14.处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2),
'yyyymmdd'),last_day(sysdate) from dual
16.找出今年的天数
select add_months(trunc(sysdate,'year'), 12) -
trunc(sysdate,'year') from dual
闰年的处理方法
to_char( last_day( to_date('02' | | :year,'mmyyyy') ),
'dd' )
如果是28就不是闰年
17.yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001
18.不同时区的处理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual;
19.5秒钟一个间隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) *
300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual
2002-11-1 9:55:00 35786
SSSSS表示5位秒数
20.一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:51
21.计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
)
select * from tabname
order by
decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
//
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
d(mod(date2-date1, 365), 30)作为日.
23.next_day函数返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日一二三四五六
---------------------------------------------------------------
select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
日期返回的是天然后转换为ss
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
select sysdate S1,
trunc(sysdate) S2, //返回当前日期,无时分秒
trunc(sysdate,'year') YEAR, //返回当前年的1月1日,无时分秒
trunc(sysdate,'month') MONTH , //返回当前月的1日,无时分秒
trunc(sysdate,'day') DAY //返回当前星期的星期天,无时分秒
from dual
26,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
27.计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日
select floor(to_number(sysdate-to_date('2007-11-02
15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from
dual //时间差-年
select ceil(moths_between(sysdate-to_date('2007-11-02
15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //时间差-月
select floor(to_number(sysdate-to_date('2007-11-02
15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from
dual //时间差-天
select floor(to_number(sysdate-to_date('2007-11-02
15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from
dual //时间差-时
select floor(to_number(sysdate-to_date('2007-11-02
15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from
dual //时间差-分
select floor(to_number(sysdate-to_date('2007-11-02
15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒
28.更新时间
注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-年
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss'),add_months(sysdate,n) as newTime from
dual //改变时间-月
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-日
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-时
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-分
select to_char(sysdate,'yyyy-mm-dd
hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-秒
29.查找月的第一天,最后一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH')
First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400
Last_Day_Cur_Month
FROM dual;
30,查询一年12个月的信息
Select to_char(add_months(trunc(sysdate,'year'),Rownum -
1),'yyyy-mm')
From dual Connect By Rownum <= 12
1 2009-01
2 2009-02
。。。
11 2009-11
12 2009-12
31、查询每月30天的信息
Select to_char(trunc(sysdate,'month') + Rownum - 1,'yyyy-mm-dd') From dual Connect By Rownum <= extract(Day From
last_day(trunc(Sysdate,'month')))
1 2009-07-01
2 2009-07-02
3 2009-07-03
。。。
30 2009-07-30
31 2009-07-31
32、目前时间日期信息
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; return Current Year ? SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; return Current Month ? SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; return Current Day
Oracle EXTRACT()函数
EXTRACT extracts and returns the value of a specified datetime field
from a datetime or interval value expression.
The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE datatype. eg.
?SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; return Current Year ?SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; return Current Month ?SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; return Current Day ?SELECT EXTRACT(HOUR FROM SYSDATE) FROM DUAL;Error
?Correct:SELECT EXTRACT(HOUR FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;
o SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DUAL;Error
Correct:SELECT EXTRACT(MINUTE FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;
Oracle列转行和行转列的几种用法 栏到 栏主要讨论sys_connect_by_path的用法 1，具有分层关系 SQL > createtabledept(deptnononumber，deptname varchar2 (20)，mgrnononumber)； table created . SQL >插入deptvalues (1，“总部”，空)； 1 row created . SQL >插入deptvalues (2，’浙江分公司’，1)； 1 row created . SQL > insert into dept values(3，’杭州分公司’，2)；已创建 1行。 SQL >提交； 提交完成。 SQL >从部门连接中选择最大值(子串(sys_connect_by_path(deptname，’，’)，2))由先前部门连接= mgrno 最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(DEPTNAME)，’)，2) -总部，浙江分行，杭州分行 2，行-列转换 如果一个表的所有列都连接到一行，用逗号分隔:
SQL >选择最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(column _ name，’，’)，2)) MAX(SUBSTRA(SYS _ CONNECT _ BY _ PATH(COLUMN _ NAME，’，’)，2)) - DEPTNO，DEPTNAME，MGRNO 3，ListAgg(Oracle 11g) SQL >选择DEPTNO， 2 ListAgg(NAME，’；’) 3在组 4内(由搪瓷订购)搪瓷 5来自emp 6组由deptno 7由deptno 8 / DEPTNO搪瓷 - - 10 CLARK。国王；米勒 20亚当斯；福特。琼斯； SCOTT。史密斯 30艾伦；布莱克； JAMES；马丁； TURNER；下面的W ARD
sqlserver到oracle数据无损迁移 编者：liuli10@https://www.doczj.com/doc/699117993.html, 版本：V1.7 最后修订日期：2015-11-21
第一章简介 1.1数据迁移 随着时代发展数据越来越被重视，而很多时候，当系统需要更新换代的时候，升级后系统所是有的数据库与当前系统的数据库并不一致，此时不仅需要数据割接，最重要的是：如何能将老系统中的数据无损的割接到新系统、新数据库中。因此，结合项目实战经验，针对从windows平台下数据库sqlserver到linux平台下oracle数据库的数据无损迁移进行总结。 1.2数据库简介 一般此处会有很多数据库以及出品公司的历史以及发展历程，在编者看来然而并没有什么大用途，百度百科都可以搜索的到，因此本章结束，直接进入实战总结环节。
第二章sqlserver数据导出 2.1sqlserver数据导出命令 当然不可否认windows为sqlserver提供了强大的图形化平台，导出数据变得只需要点一点就能完成，然而这样的数据导出对于大批量有要求的操作，是极其劳神伤财的，因此，必须要通过命令行进行格式化导出，因此，这里介绍sqlserver 本机数据库导出命令。 2.1.1bcp命令以及参数介绍 https://www.doczj.com/doc/699117993.html,/liyanmingkong/article/details/6087674 https://www.doczj.com/doc/699117993.html,/uid-25472509-id-4304562.html https://www.doczj.com/doc/699117993.html,/link?url=WV2JJM4JHxR7Qct8rr_-499zPc3aP_7E5rOt5l yEnG_Mj_tE9_-ZN1JPE2Vc2wRpkO8QkNGNLVznDfMgniCOnxXhK5jQppNpZk8 Jo1x8o23 为了将文档尽可能精简，bcp命令的参数以及介绍请自行去以上任意网址查询。或者自行baidu或者google搜索。 2.2实战语句解析 实战语句为： bcp"select*from gwbnboss.dbo.ACCOUNT_BUSINESS"queryout "C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt"-c -r"{#$&}"-t"{@#$}"-S"127.0.0.1"-U"数据库用户名"-P"密码" 最终导出的结果存在于 C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt 文件中，当出现“{#$&}”时表示接下来是下一行数据，出现“{@#$}”时表示接下来是下一列数据。将查询结果集完整导出，不对数据做任何格式化或者修改操作，保证数据的原生无损。
sysdate+(5/24/60/60) 在系统时间基础上延迟5秒 sysdate+5/24/60 在系统时间基础上延迟5分钟 sysdate+5/24 在系统时间基础上延迟5小时 sysdate+5 在系统时间基础上延迟5天 add_months(sysdate,-5) 在系统时间基础上延迟5月 add_months(sysdate,-5*12) 在系统时间基础上延迟5年 上月末的日期：select last_day(add_months(sysdate, -1)) from dual; 本月的最后一秒：select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual 本周星期一的日期：select trunc(sysdate,'day')+1 from dual 年初至今的天数：select ceil(sysdate - trunc(sysdate, 'year')) from dual; 今天是今年的第几周 :select to_char(sysdate,'fmww') from dual 今天是本月的第几周:SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual 本月的天数 SELECT to_char(last_day(SYSDATE),'dd') days FROM dual 今年的天数 select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 下个星期一的日期 SELECT Next_day(SYSDATE,'monday') FROM dual ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ --计算工作日方法 create table t(s date,e date); alter session set nls_date_format = 'yyyy-mm-dd'; insert into t values('2003-03-01','2003-03-03'); insert into t values('2003-03-02','2003-03-03'); insert into t values('2003-03-07','2003-03-08'); insert into t values('2003-03-07','2003-03-09'); insert into t values('2003-03-05','2003-03-07'); insert into t values('2003-02-01','2003-03-31'); -- 这里假定日期都是不带时间的，否则在所有日期前加trunc即可。 select s,e,e-s+1 total_days, trunc((e-s+1)/7)*5 + length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'
--当期时间贷款时间 SELECT DK_ID, max(substr(activeDate, 2)) activeDate FROM (SELECT DK_ID, sys_connect_by_path(activeDate, ',') activeDate FROM (SELECT DK_ID, activeDate, DK_ID || rn rchild, DK_ID || (rn - 1) rfather FROM (SELECT TEMP.DK_ID, --查询项目所在地树形结构全名 SELECT t.area_id, substr(sys_connect_by_path(t.area_name, '-'), 2) as allname , connect_by_root t.area_name as root, --是单一操作符，返回当前层的最顶层节点connect_by_isleaf as IsLeaf, --是伪列，判断当前层是否为叶子节点，1代表是，0代表否 level as lel --是伪列，显示当前节点层所处的层数 FROM dk_project_area_info t START WITH t.area_name = '项目所在地' CONNECT BY PRIOR t.area_id = t.area_pid SYS_CONNECT_BY_PATH 学习2008-09-08 10:59SELECT ename FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; 得到结果为：KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES
TO_DATE格式(以时间:2007-11-02 13:45:25为例) Year: yy two digits 两位年显示值:07 yyy three digits 三位年显示值:007 yyyy four digits 四位年显示值:2007 Month: mm number 两位月显示值:11 mon abbreviated 字符集表示显示值:11月,若是英文版,显示nov month spelled out 字符集表示显示值:11月,若是英文版,显示november Day: dd number 当月第几天显示值:02 ddd number 当年第几天显示值:02 dy abbreviated 当周第几天简写显示值:星期五,若是英文版,显示fri day spelled out 当周第几天全写显示值:星期五,若是英文版,显示friday ddspth spelled out, ordinal twelfth Hour: hh two digits 12小时进制显示值:01 hh24 two digits 24小时进制显示值:13 Minute: mi two digits 60进制显示值:45 Second: ss two digits 60进制显示值:25 其它 Q digit 季度显示值:4 WW digit 当年第几周显示值:44 W digit 当月第几周显示值:1 24小时格式下时间范围为： 0:00:00 - 23:59:59.... 12小时格式下时间范围为： 1:00:00 - 12:59:59 .... 1. 日期和字符转换函数用法（to_date,to_char） select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串 select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年 select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月 select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日
表结构和数据如下（表名Test）： NO V ALUE NAME 1 a 测试1 1 b 测试2 1 c 测试3 1 d 测试4 2 e 测试5 4 f 测试6 4 g 测试7 Sql语句： select No, ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value, ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name from (select No, Value, Name, rnFirst, lead(rnFirst) over(partition by No order by rnFirst) rnNext from (select a.No, a.Value, https://www.doczj.com/doc/699117993.html,, row_number() over(order by a.No, a.V alue desc) rnFirst from Test a) tmpTable1) tmpTable2 start with rnNext is null connect by rnNext = prior rnFirst group by No; 检索结果如下： NO V ALUE NAME 1 a;b;c;d 测试1;测试2;测试3;测试4 2 e 测试5 4 f;g 测试6;测试7 简单解释一下那个Sql吧： 1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：select a.No, a.Value, https://www.doczj.com/doc/699117993.html,, row_number() over(order by a.No, a.V alue desc) rnFirst
Oracle时间日期操作 sysdate+(5/24/60/60) 在系统时间基础上延迟5秒 sysdate+5/24/60 在系统时间基础上延迟5分钟 sysdate+5/24 在系统时间基础上延迟5小时 sysdate+5 在系统时间基础上延迟5天 add_months(sysdate,-5) 在系统时间基础上延迟5月 add_months(sysdate,-5*12) 在系统时间基础上延迟5年 上月末的日期：select last_day(add_months(sysdate, -1)) from dual; 本月的最后一秒：select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual 本周星期一的日期：select trunc(sysdate,'day')+1 from dual 年初至今的天数：select ceil(sysdate - trunc(sysdate, 'year')) from dual; 今天是今年的第几周:select to_char(sysdate,'fmww') from dual 今天是本月的第几周:SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual 本月的天数 SELECT to_char(last_day(SYSDATE),'dd') days FROM dual 今年的天数 select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 下个星期一的日期 SELECT Next_day(SYSDATE,'monday') FROM dual ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ --计算工作日方法 create table t(s date,e date); alter session set nls_date_format = 'yyyy-mm-dd'; insert into t values('2003-03-01','2003-03-03'); insert into t values('2003-03-02','2003-03-03'); insert into t values('2003-03-07','2003-03-08'); insert into t values('2003-03-07','2003-03-09'); insert into t values('2003-03-05','2003-03-07'); insert into t values('2003-02-01','2003-03-31'); -- 这里假定日期都是不带时间的，否则在所有日期前加trunc即可。 select s,e,e-s+1 total_days,
关于动态SQL的使用-----摘录 内容摘要：在PL/SQL开发过程中，使用SQL，PL/SQL可以实现大部份的需求，但是在某些特殊的情况下，在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的需求，比如需要动态建表或某个不确定的操作需要动态执行。这就需要使用动态SQL来实现。本文通过几个实例来详细的讲解动态SQL的使用。 本文适宜读者范围：Oracle初级，中级 系统环境： OS：windows2000Professional(英文版) Oracle：8.1.7.1.0 正文： 一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。 首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。 Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明： 一、本地动态SQL 本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。 1、本地动态SQL执行DDL语句： 需求：根据用户输入的表名及字段名等参数动态建表。 create or replace procedure proc_test ( table_name in varchar2,--表名 field1in varchar2,--字段名 datatype1in varchar2,--字段类型 field2in varchar2,--字段名 datatype2in varchar2--字段类型 )as str_sql varchar2(500); begin str_sql:=create table||table_name||(||field1||||datatype1||,||field2|| ||datatype2||); execute immediate str_sql;--动态执行DDL语句 exception when others then null; end; 以上是编译通过的存储过程代码。下面执行存储过程动态建表。
ORACLE时间函数: sysdate+(5/24/60/60) 在系统时间基础上延迟5秒 sysdate+5/24/60 在系统时间基础上延迟5分钟 sysdate+5/24 在系统时间基础上延迟5小时 sysdate+5 在系统时间基础上延迟5天 add_months(sysdate,-5) 在系统时间基础上延迟5月 add_months(sysdate,-5*12) 在系统时间基础上延迟5年 上月末的日期：select last_day(add_months(sysdate, -1)) from dual; 本月的最后一秒：select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual 本周星期一的日期：select trunc(sysdate,'day')+1 from dual 年初至今的天数：select ceil(sysdate - trunc(sysdate, 'year')) from dual; 今天是今年的第几周 :select to_char(sysdate,'fmww') from dual 今天是本月的第几周:SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual 本月的天数 SELECT to_char(last_day(SYSDATE),'dd') days FROM dual 今年的天数 select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 下个星期一的日期 SELECT Next_day(SYSDATE,'monday') FROM dual ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ ＝＝＝＝＝ --计算工作日方法 create table t(s date,e date); alter session set nls_date_format = 'yyyy-mm-dd'; insert into t values('2003-03-01','2003-03-03'); insert into t values('2003-03-02','2003-03-03'); insert into t values('2003-03-07','2003-03-08'); insert into t values('2003-03-07','2003-03-09'); insert into t values('2003-03-05','2003-03-07'); insert into t values('2003-02-01','2003-03-31'); -- 这里假定日期都是不带时间的，否则在所有日期前加trunc即可。 select s,e,e-s+1 total_days, trunc((e-s+1)/7)*5 + length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7
先来个简单的用法 列转行 Create table test (name char(10),km char(10),cj int) insert test values('张三','语文',80) insert test values('张三','数学',86) insert test values('张三','英语',75) insert test values('李四','语文',78) insert test values('李四','数学',85) insert test values('李四','英语',78) select name, sum(decode(km,'语文',CJ,0)) 语文, sum(decode(km,'数学',cj,0)) 数学, sum(decode(km,'英语',cj,0)) 英语 from test1 group by name 姓名语文数学英语 张三80 86 75 李四78 85 78 行转列 with x as( selectname, sum(decode(km,'语文',CJ,0)) 语文 , sum(decode(km,'数学',cj,0)) 数学, sum(decode(km,'英语',cj,0)) 英语 fromtest groupbyname) selectname,decode(rn,1, '语文', 2, '数学', 3,'英语') 课程, decode(rn, 1, 语文, 2, 数学, 3,英语) 分数 from x, (selectlevel rn from dual connectby1=1andlevel<=3) (from 后面接两个表,是笛卡尔积)
1:取得当前日期是本月的第几周 SQL> select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YY ------------------- 20030327 4 18:16:09 SQL> select to_char(sysdate,'W') from dual; T - 4 2:取得当前日期是一个星期中的第几天,注意星期日是第一天SQL> select sysdate,to_char(sysdate,'D') from dual; SYSDATE T --------- - 27-MAR-03 5 类似: select to_char(sysdate,'yyyy') from dual; --年 select to_char(sysdate,'Q' from dual; --季 select to_char(sysdate,'mm') from dual; --月 select to_char(sysdate,'dd') from dual; --日 ddd 年中的第几天 WW 年中的第几个星期 W 该月中第几个星期 D 周中的星期几 hh 小时(12) hh24 小时(24) Mi 分 ss 秒 3:取当前日期是星期几中文显示: SQL> select to_char(sysdate,'day') from dual; TO_CHAR(SYSDATE,'DAY') ---------------------- 星期四 4:如果一个表在一个date类型的字段上面建立了索引，如何使用alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
众所周知，静态SQL的输出结构必须也是静态的。对于经典的行转列问题，如果行数不定导致输出的列数不定，标准的答案就是使用动态SQL, 到11G里面则有XML结果的PIVOT。 今天在asktom看到的一篇贴子彻底颠覆了我的看法！贴子里的链接指向另一个牛人辈出的荷兰公司： http://technology.amis.nl/2006/0 ... ing-antons-thunder/ 还记得Anton Scheffer吗？这位神人先是用10G的MODEL写了SUDOKU的一句SQL的解法，在11GR2推出之后又率先用递归WITH写了个只有短短几行的SUDOKU解法。他的作品还有EXCEL文件生成器。 早在2006年他就发明了真正动态的行转列办法，用的是一系列神秘的函数，如同自定义聚合函数STRAGG里面用的那些。这个神秘的对象代码如下： 1.CREATE OR REPLACE 2.type PivotImpl as object 3.( 4.ret_type anytype,-- The return type of the table function 5.stmt varchar2(32767), 6.fmt varchar2(32767), 7.cur integer, 8.static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) 9.return number, 10.static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) 11.return number, 12.static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) 13.return number, 14.member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset ) 15.return number, 16.member function ODCITableClose( self in PivotImpl ) 17.return number 18.) 19./ 20. 21.create or replace type body PivotImpl as 22.static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number ) 23.return number 24.is 25.atyp anytype; 26.cur integer; 27.numcols number; 28.desc_tab dbms_sql.desc_tab2; 29.rc sys_refcursor; 30.t_c2 varchar2(32767); 31.t_fmt varchar2(1000); 32.begin 33.cur := dbms_sql.open_cursor; 34.dbms_sql.parse( cur, p_stmt, dbms_sql.native ); 35.dbms_sql.describe_columns2( cur, numcols, desc_tab ); 36.dbms_sql.close_cursor( cur ); 37.-- 38.anytype.begincreate( dbms_types.typecode_object, atyp ); 39.for i in 1 .. numcols - 2
Oracle关于日期时间 一.问题描述 我安装的数据库日期Date格式默认为’DD-M-YYYY(2-2月-1988)’,而数据库默认的格式一般都是’DD-MON-YYYY’,现在需要把日期格式改成数据库原本默认的格式 二.处理方式 修改注册表： 1.在注册表的KEY_LOCAL_MACHINE->SOFTWARE->ORACLE->HOME0 2.找到：NLS_LANG 如果这个不是’American’，要设置成英文(我原来安装的时候选的是简体中文，NLS_LANG的值是：SIMPLIFIED CHINESE_CHINA.ZHS16GBK) 3.新建一个字符串，命名为NLS_DA TE_FORMA T，然后值填上想要的默认格式，例如：YYYY-MM-DD HH24:MI:SS
4.第三方的工具显示的日期格式有时候和数据库默认的不通，工具里面的格式是受工具影响的，如果想要改变PLSQL Developer里面的日期显示格式，可以这样改： PL/SQL DEVELOPER需要在Tools->Preferences->User Interface->Date/Time下修改格式即 可： 如修改参数文件的话。可直接在参数文件中添加：
NLS_DATA_FORMAT=YYYY-MM-DD-HH24:MI:SS，但须将注册表中上述位置的NLS_LANG字串值去掉。 三.常用的命名 1.可以用下面的命令来修改一个会话的日期格式，断开与数据库的连接时，会话结束， 所有的修改都会丢失 alter session set nls_date_format='yyyy-mm-dd' 2. 查看日期格式命名：show parameter nls_date_format 3. 查看参数设置：select * from nls_session_parameters 4．求某天是星期几select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 5.求当前是星期几：select to_char(sysdate, 'day') from dual 6. 一年的第几天：select TO_CHAR(SYSDA TE,'DDD'),sysdate from dual 参考网址：https://www.doczj.com/doc/699117993.html,/397879213/archive/2010/12/29/341925.html https://www.doczj.com/doc/699117993.html,/gudufeiyang/article/details/5938885 2011年7月9日星期六 ___LiBo整理
oracle列转行 1------------------------ 表结构： 1A 1B 1C 2A 2B 3C 3F 4D 转换后变成： 1A,B,C 2A,B 3C,F 4D 假设你的表结构是tb_name(id,remark),则语句如下:
SELECT a.id, wm_concat(a.remark)new_result FROM tb_name a group by a.id 2---------------------------------- 产品名称销售额季度 奶酪50第一季度 奶酪60第二季度 啤酒50第二季度 啤酒80第四季度 。。。 。。。 想转换成如下格式 产品名称第一季度销售额第二季度销售额第三季度销售额第四季度销售额 奶酪50600 0 啤酒0500
80 oracle下可以用函数decode处理: select产品名称, sum(decode(季度,'第一季度',销售额,0))第一季度销售额, sum(decode(季度,'第二季度',销售额,0))第二季度销售额, sum(decode(季度,'第三季度',销售额,0))第三季度销售额, sum(decode(季度,'第四季度',销售额,0))第四季度销售额, from表名 group by产品名称; 3------------------------------------------- oracle行转列的通用过程2010-04-0923:28经常遇到发帖求行列转换的代码，用max(decode(..))回复后，十有八九会再问一句：如果列名不固定，或者列数不固定怎么办。就要用存储过程来写，这些存储过程的代码都大同小异，我就想能不能写个通用点的过程
Oracle中日期,时间查询 在注册表\hkey_local_machine\software\oracle\home0主键中增加一个字串(8i版本)，字串名为nls_date_format，字串的值为你希望定义的时间格式，如：yyyy-mm-dd hh24:mi:ss to_date('2007-08-25 19:31:00','yyyy-mm-dd hh24:mi;ss') 在oracle中处理日期大全 TO_DATE格式 Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out march Year: yy two digits 98 yyyy four digits 1998 24小时格式下时间范围为： 0:00:00 - 23:59:59.... 12小时格式下时间范围为： 1:00:00 - 12:59:59 .... 1. 日期和字符转换函数用法（to_date,to_char） 2. select to_char( to_date(222,'J'),'Jsp') from dual 显示Two Hundred Twenty-Two 3. 求某天是星期几 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 星期一 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; monday 设置日期语言
未来社会模型中 SaaS 的位置与分量 上图是一个从连接这个透视角度抽象出来的社会模型，其中的家庭、人、组织、物都是相互连接的，它也是一个从软件架构抽象出来的社会模型。SaaS 是对软件的获得和使用方式的革命，早在 2004 年就已有端倪（当时称为 ASP，Application Service Provider）。笔者认为，可以将 SaaS 放在社会运行机制、发展趋势这样的大格局中定位其社会作用。SaaS 企业也需要这样的格局与信念，虽然在中国还没有出现非常成功的 SaaS 企业，但终究会出现的，信任、习惯、规范、与能力都需要进化，需要时间。 在没有电之前，人们就有传递信息的需求，这也是为什么微信能够存在的本质根由。同理，各种组织都离不开软件，而且软件的渗透越来越广泛与深入，因为整个世界的数字化是不可阻挡的趋势。而 SaaS 在大部分情况下是必选之路，会越来越成为标配，除非特殊原因，或者不在乎成本、或者已经拥有某种等效的软件、或者其他原因。只要这种本质性的需求存在着，社会的发展终究会以越来越先进的方式来满足它。 这里分享一个关于云的小故事，笔者曾经算过一笔账，如果在云上订购托管机房中约 500 台机器的同等算力，每年需要支出 5000 万，相当有悖于流行认知，其实对于稍微有些规模的 IT 资源诉求，云相对是更加昂贵的，但来的快、方便，两方面都是事实。
这里只想传递一个观点，长远来看，SaaS 有它存在与发展的必然性。结构上讲，它是社会运行机制中不可或缺的一部分。同样或者类似的软件，显然没有必要每个人、每个组织都各买一套或各自开发一套，这是社会资源的极大浪费，有悖于社会发展的基本规律——既然是必需的，必然选择物美价廉。而且组织支出比个人支出更理性、更注重实用价值，有利可图的需求终究会达到稳态的、某种主流服务的满足。 从架构角度看 SaaS 面临的挑战 如果说 SaaS 在大部分情况下将会成为必选之路，那么它面临的最大挑战又是什么呢？概括来讲，SaaS 面临的最大挑战是满足客户的个性化需求。从架构角度看，它体现在如下图所示的几个方面： 其中最有挑战性的又要属多变的后台逻辑与数据模型。对于 SaaS 供应商而言，这种需求显然不能通过项目的方式来定制满足，而只有通过提供灵活的自服务平台才能满足，这种灵活性就需要用 PaaS 来生产客户想要的软件。这一点笔者在2013 年做一个 SaaS 项目的架构工作时就深有体会：一开始的目标也是做 SaaS，但是后来还是走上了 PaaS 的道路，不过是专为生产 SaaS 而自用的 PaaS，而不是定位于 PaaS 供应商。 如果一个 SaaS 企业从一开始就只是聚焦某个业务，而没有着手 PaaS 的建设，那说明它在满足个性化需求的道路上一定是在某个局部、某个层面解决问题，而不是系统、全面、可复用地解决问题。同时，从中国 SaaS 市场现状来看，它的成长比较慢，环境的综合成熟度还不够高，聚焦单一业务成长的加速度不够，因此企业后续很可能会从最开始聚焦的核心业务向外扩展，到时候又要面临种种个性化需求问题。因此，一个成功的 SaaS 企业必然要去寻求 PaaS 的支撑。从这两个意义上讲，PaaS 可能也是 SaaS 企业提高生产力的必经方向。据有关数据
Oracle 数据类型及存储方式(三)日期时间类型——袁光东[转] 2009年10月18日星期日 16:57 第三部分日期时间类型 §3.1 DATE Date类型Oralce用于表示日期和时间的数据类型。固定占用7个字节。 包括七个属性： 世纪 世纪中的年份 月份 月份中的哪一天 小时 分 秒 SQL> create table test_date(col_date date); Table created SQL> insert into test_date values(to_date('2008-06-27 10:35:00','yyyy-mm-dd hh24:mi:ss')); 1 row inserted SQL> select to_char(col_date,'yyyy-mm-dd hh24:mi:ss'),dump(col_date) from test_date; TO_CHAR(COL_DATE,'YYYY-MM-DDHH DUMP(COL_DATE) ------------------------------ -------------------------------------------------------------------------------- 2008-06-27 10:35:00 Typ=12 Len=7: 120,108,6,27,11,36,1 Date类型的内部编码为12 长度:占用7个字节 数据存储的每一位到第七位分别为：世纪，年，月，日，时，分，秒。 世纪：采用”加100”表示法来存储。即世纪+100来存储。120 – 100 = 20 年:跟世纪一样采用”加100”表示法来存储。108 – 100 = 08(采用两位表示) 月:自然存储.6 日:自然存储，不做修改，27 时：（时，分，秒都采用“加1”法存储）11 -1= 10 分:36 -1 = 35 秒:1 -1 = 0 为什么世纪和年份要用加100法存储呢？是为了支持BC和AD日期。 BC即为公元前。 AD即为公元。 如果世纪– 100为一个负数，那么就是一个BC日期。