to_date()就是将一个数字变量转换成你需要的时间变量,比如说你要查询一个门店日报中最近10天的销售情况: select store.code,store.name,goods.code,goods.name,sdrpt.iamt,sdrpt.itax,sdrpt.amt,sdrpt.tax,sdrpt.qty from sdrpt,goods,sdrpt where good.gid=store.gdgid goods.gid=sdrpt.gdgid and store.gid=12345 and sdrpt.fildate>=to_date('2003.01.01 10:10:10','yyyy.mm.dd hh24:mi:ss') and sdrpt.fildate<=to_date('2003.01.10 10:10:10','yyyy.mm.dd hh24:mi:ss')以上:store 是店表 goods 商品表 sdrpt 是门店销售日报 gid 各个表中的主键 code name code name iamt itax amt tax qty 门店代码,门店名称,商品代码,商品名称,进价金额,进价税额,售价金额,售价税额,数量
Purpose TO_DATE converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion. Do not use the TO_DATE function with a DATE value for the char argument. The first 2 digits of the returned DATE value can differ from the original char, depending on fmt or the default date format. See Also: "Date Format Models"
Example INSERT INTO bonus (bonus_date) SELECT TO_DATE( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL;
to_date(string,[format,[nlsparams]])函数将char 或varchar2类型的值转化为一个Date值。 format,nlsparams是可选项;format 指定了要转化的格式;nlsparams指定了返回日期所使用的语言,格式为:'NLS_DATE_LANGUAGE=LANGUAGE';如果没有指定format和nlsparams,则按oracle的默认模式处理。如下例: 1)select to_date('2003 JULY 21','YYYY-MM-DD','NLS_DATE_LANGUE=english)"result" from dual; 2)select to_date('20030411155050','YYYY-MM-DD HH24MISS') FROM DUAL;
TO_DATE(char [, fmt [, 'nlsparams'] ]) 1、SELECT TO_DATE('January 15, 2003, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.','NLS_DATE_LANGUAGE = American') as m from user输出结果:2003-1-15 11:00:002、SELECT TO_DATE('20030302','YYYY-MM-DD') as m from user输出结果:2003-3-2
select store.code,store.name,goods.code,goods.name,sdrpt.iamt,sdrpt.itax,sdrpt.amt,sdrpt.tax,sdrpt.qty
from sdrpt,goods,sdrpt
where good.gid=store.gdgid
goods.gid=sdrpt.gdgid
and store.gid=12345
and sdrpt.fildate>=to_date('2003.01.01 10:10:10','yyyy.mm.dd hh24:mi:ss')
and sdrpt.fildate<=to_date('2003.01.10 10:10:10','yyyy.mm.dd hh24:mi:ss')以上:store 是店表 goods 商品表 sdrpt 是门店销售日报 gid 各个表中的主键
code name code name iamt itax amt tax qty
门店代码,门店名称,商品代码,商品名称,进价金额,进价税额,售价金额,售价税额,数量
TO_DATE converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion. Do not use the TO_DATE function with a DATE value for the char argument. The first 2 digits of the returned DATE value can differ from the original char, depending on fmt or the default date format. See Also: "Date Format Models"
Example
INSERT INTO bonus (bonus_date)
SELECT TO_DATE(
'January 15, 1989, 11:00 A.M.',
'Month dd, YYYY, HH:MI A.M.',
'NLS_DATE_LANGUAGE = American')
FROM DUAL;
format,nlsparams是可选项;format 指定了要转化的格式;nlsparams指定了返回日期所使用的语言,格式为:'NLS_DATE_LANGUAGE=LANGUAGE';如果没有指定format和nlsparams,则按oracle的默认模式处理。如下例:
1)select to_date('2003 JULY 21','YYYY-MM-DD','NLS_DATE_LANGUE=english)"result" from dual;
2)select to_date('20030411155050','YYYY-MM-DD HH24MISS') FROM DUAL;