数据库SQL中的数据类型转换

oracle有三种最基本的数据类型，即字符型、数值型、日期型。 (注意需要起别名的地方要起别名)
oracle提供的单行函数中，针对不同的数据类型，提供大量实用的函数，同时提供一系列数据类型转换函数，如下： 

sql.append(" select CURVE_NAME,S_YEAR_LIMIT,AVERAGE_VALUE,to_char(d_date,'yyyy-MM-dd') d_date from t_treasury_yield_curve t where d_date = to_date ('"+d_date+"','yyyy-MM-dd')");

24 小时的形式显示出来要用 HH24


select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;


select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;



to_date() function

1. 日期格式参数 含义说明

D 一周中的星期几


DAY 天的名字，使用空格填充到 9 个字符


DD 月中的第几天


DDD 年中的第几天


DY 天的简写名


IW ISO 标准的年中的第几周


IYYY ISO 标准的四位年份


YYYY 四位年份


YYY,YY,Y 年份的最后三位，两位，一位


HH 小时，按 12 小时计


HH24 小时，按 24 小时计


MI 分


SS 秒


MM 月


Mon 月份的简写


Month 月份的全名


W 该月的第几个星期


WW 年中的第几个星期      1. 日期时间间隔操作


当前时间减去 7 分钟的时间


select sysdate,sysdate - interval '7' MINUTE from dual


当前时间减去 7 小时的时间


select sysdate - interval '7' hour from dual


当前时间减去 7 天的时间


select sysdate - interval '7' day from dual


当前时间减去 7 月的时间


select sysdate,sysdate - interval '7' month from dual


当前时间减去 7 年的时间


select sysdate,sysdate - interval '7' year from dual


时间间隔乘以一个数字


select sysdate,sysdate - 8 *interval '2' hour from dual


 


2. 日期到字符操作


select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual


select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual


select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual


select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual


参考 oracle 的相关关文档 (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)


 


3. 字符到日期操作


select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual


具体用法和上面的 to_char 差不多。


4.TO_NUMBER 
使用TO_NUMBER函数将字符转换为数字 
TO_NUMBER(char[, '格式'])


数字格式格式 
9 代表一个数字 
0 强制显示0 
$ 放置一个$符 
L 放置一个浮动本地货币符 
. 显示小数点 
, 显示千位指示符


oracle中的to_date参数含义
 
  1.日期格式参数含义说明  
D 一周中的星期几  
DAY 天的名字，使用空格填充到9个字符  
DD 月中的第几天  
DDD 年中的第几天  
DY 天的简写名  
IW ISO标准的年中的第几周  
IYYY ISO标准的四位年份  
YYYY 四位年份  
YYY,YY,Y 年份的最后三位，两位，一位  
HH 小时，按12小时计  
HH24 小时，按24小时计  
MI 分  
SS 秒  
MM 月  
Mon 月份的简写  
Month 月份的全名  
W 该月的第几个星期  
WW 年中的第几个星期     1.日期时间间隔操作
当前时间减去7分钟的时间
select sysdate,sysdate - interval ’7’ MINUTE from dual
当前时间减去7小时的时间
select sysdate - interval ’7’ hour from dual
当前时间减去7天的时间
select sysdate - interval ’7’ day from dual
当前时间减去7月的时间
select sysdate,sysdate - interval ’7’ month from dual
当前时间减去7年的时间
select sysdate,sysdate - interval ’7’ year from dual
时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval ’2’ hour from dual
   2.日期到字符操作 
select sysdate,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual
select sysdate,to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual
select sysdate,to_char(sysdate,’yyyy-ddd hh:mi:ss’) from dual
select sysdate,to_char(sysdate,’yyyy-mm iw-d hh:mi:ss’) from dual
参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
   3. 字符到日期操作 
select to_date(’2003-10-17 21:15:37’,’yyyy-mm-dd hh24:mi:ss’) from dual
具体用法和上面的to_char差不多。
   4. trunk/ ROUND函数的使用 
select trunc(sysdate ,’YEAR’) from dual
select trunc(sysdate ) from dual
select to_char(trunc(sysdate ,’YYYY’),’YYYY’) from dual
    5.oracle有毫秒级的数据类型 
--返回当前时间 年月日小时分秒毫秒
select to_char(current_timestamp(5),’DD-MON-YYYY HH24:MI:SSxFF’) from dual;
--返回当前时间的秒毫秒，可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(9),’MI:SSxFF’) from dual;
   6.计算程序运行的时间(ms) 
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for I in 1 .. 1000
loop
open l_rc for
’select object_name from all_objects ’||
’where object_id = ’ || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
’ seconds ...’ );
　　end;
（以下部分转自网络）

1）to_char 
    数值、日期->字符型 
    语法：to_char(num|date,[format mask],[nls_parameters]) 
    参数：num|date 待转换的数值或者日期 
             format mask:可选参数


数字->字符型的可用格式
格式元素
元素说明
格式
数字
字符结果


9


数字宽度


9999


12


12


0


显示前面的零


09999


0012


00012


.


小数点的位置


09999.999


030.40


00030.400


D


小数点分隔符的位置(默认为句点)


09999D999


030.40


00030.400


,


逗号的位置


09999,999


03040


00003,040


G


组分隔符的位置(默认为逗号)


09999G999


03040


00003,040


$


美元符号


$099999


03040


$003040


L


当地货币


L099999


03040


GBP003040如果nls_currency设置为GBP


MI


表示负数的减号的位置


99999MI


-3040


3040-


PR


包围在括号内的负数


99999PR


-3040


<3040>


EEEE


科学计数法


99.99999EEEE


121.976


1.21976E+02


U


Nls_dual_currency


U099999


03040


CAD003040如果nls_dual_currency设置为CAD


V


乘以10n次(n是V之后9的数量)


9999V99


3040


304000


S


前面加上+或者-


S999999


3040


+3040


  


日期->字符型的可用格式(示例日期:02-JUN-1975)


格式说明


说明


结果


Y


年的最后一位


5


YY


年的最后两位


75


YYY


年的最后三位


975


YYYY


四位数字表示的年


1975


RR


两位数字表示的年


75


YEAR


区别大小写并用英语拼写的年


NINETEEN SEVENTY-FIVE


MM


两位数表示的月


06


MON


月的三个字母缩写


JUN


MONTH


区分大小写并用英语拼写的月


JUNE


D


星期几


2


DD


月的两位数日(即是本月的第几天)


02


DDD


年的日


153


DY


星期的三个字母缩写


MON


DAY


区分大小写并用英语拼写的星期


MONDAY


 


其他不常用的格式掩码


演示数据：24-SEP-1000 BC


 


格式元素


说明


结果


W


月的周数


4


WW


年的周数


39


Q


年的季度


3


CC


世纪


10


S preceding CC,YYYY,YEAR


如果日期是BC,那么减号就在结果之前


-10,-1000或者-ONE THOUSAND


IYYY,IYY,IY,I


分别表示4,3,2,1为ISO日期


1000,000,00,0


BC,AD,B.C. and A.D.


 


 


J


儒略日—从公元前4713年12月31日开始的天数


1356075


IW


ISO标准周(1到53)


39


RM


用罗马数字表示的月


IX


 


时间组件的日期格式掩码


演示数据:27-JUN-2010 21:35:13


 


格式元素


说明


结果


AM,PM,A.M.和P.M.


子午线指示器


PM


HH,HH12,HH24


一天的小时,1-12时和0-23时


09,09,21


MI


分(0-59)


35


SS


秒(0-59)


12


SSSSS


午夜之后的秒(0-86399)


77713


 


其他日期格式掩码


格式元素


说明和格式掩码


结果


-/.,?#!


标点符号:’MM.YY’


09.08


“any character literal”


字符值:’”Week” W “”of” Month’


Week 2 of September


TH


位置或者序数文本:’DDth”of”Month’


12TH of September


SP


拼写出数字：’MmSP Month YYYYSP’


Nine September Two Thousand Eight


THSP or SPTH


拼写出位置或者序数:’hh24SpTh’


Fourteenth


 


2）to_date


字符值->日期值 
语法：to_date (string,[format mask],[nls_parameters]) 
参数：string 待转换的字符值 
        format mask:可选参数 ，格式掩码同to_char转换为date时相同。


备注：转换时要根据给定的string设定正确的格式掩码，否则


        Ora_01840:input value is not long enough for date format.


        Ora_01862:the numeric value does not match the length of the format item.


3） to_number


字符值->数字值 
语法：to_number (string,[format mask],[nls_parameters]) 
参数：string 待转换的字符值 
        format mask:可选参数，格式掩码同to_char转换为number时相同。


备注：如果使用较短的格式掩码就会返回错误。


        例如: to_number(123.56,’999.9’)返回错误。


 


在oracle中，如果不同的数据类型之间关联，如果不显式转换数据，则它会根据以下规则对数据进行隐式转换


1) 对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型。


   假如id列的数据类型为number


   update t set id='1'; -> 相当于 update t set id=to_number('1');


   insert into t(id) values('1') -> insert into t values(to_number('1'));


 


2) 对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型。
   如假设id列的数据类型为varchar2


   select * from t where id=1; -> select * from t where to_number(id)=1;


   但如果id列的数据类型为number，则


   select * from t where id='1'; -> select * from t where id=to_number('1');(参考下文)


 


3) 当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。


    如假设id列的数据类型为number


    select * from t where id='1'; -> select * from t where id=to_number('1');


 


4) 当比较字符型和日期型的数据时，oracle会把字符型转换为日期型。


    如假设create_date为字符型，


    select * from t where create_date>sysdate; -> select * from t where to_date(create_date)>sysdate;(注意，此时session的nls_date_format需要与字符串格式相符)


    假设create_date为date型，


    select * from t where create_date>'2006-11-11 11:11:11'; -> select * from t where   create_date>to_date('2006-11-11 11:11:11'); (注意，此时session的nls_date_format需要与字符串格式相符)


 


5) 如果调用函数或过程等时，如果输入参数的数据类型与函数或者过程定义的参数数据类型不一直，则oracle会把输入参数的数据类型转换为函数或者过程定义的数据类型。


   如假设过程如下定义 p(p_1 number)


   exec p('1'); -> exec p(to_number('1')); 6)


   赋值时，oracle会把等号右边的数据类型转换为左边的数据类型。


   如 var a number a:='1'; - > a:=to_number('1');


 


6) 用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型。


    select 1||'2' from dual; -> select to_char(1)||'2' from dual;


 


7) 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等。


    如果CHAR/VARCHAR2 和NCHAR/NVARCHAR2之间作算术运算，


    则oracle会将她们都转换为number类型的数据再做比较。


 


8) 比较CHAR/VARCHAR2 和NCHAR/NVARCHAR2时，如果两者字符集不一样，则默认的转换方式是将数据编码从数据库字符集转换为国家字符集。