create table t(a date); insert into t values(to_date('2008-2-1','yyyy-mm-dd')); -- select to_char(a,'yyyy')||'-'||to_number(to_char(a,'mm'))||'-'||to_number(to_char(a,'dd')) from t -- 2008-2-1
不知道是能否通过使用一个TOCHAR,是否没有这样的参数
SELECT replace(to_char(sysdate,'yyyy-mm-dd'),'-0','-') from dual
设置nls_date_format这个参数不就可以了?SQL> alter session set nls_date_format='yyyy-mm-dd';会话已更改。SQL> select sysdate from dual;SYSDATE ---------- 2008-02-27
如果数据库该字段是VARCHAR2 型,请转成日期格式再转 ,如下:select replace(to_char(to_date('2008-02-01','yyyy-mm-dd'),'yyyy-mm-dd'),'-0','-') from dualResult ---------- 2008-2-1
try: select to_char(sysdate,'yyyy') ||'-'|| to_char(to_number(to_char(sysdate,'mm'))) ||'-'|| to_char(to_number(to_char(sysdate,'dd'))) from dual
谢谢楼上各位了感觉最简单的是 select to_char( to_date('20080101','YYYYMMDD ') ,'FMYYYY-MM-DD') from dual;
select to_char(sysdate,'yyyy')||'-'||to_number(to_char(sysdate,'mm'))||'-'||to_char(sysdate,'dd') from dual;
效率而言,select to_char(sysdate,'rrrr-mm-dd') from dual;一定比多次的转换好 年||月||日
insert into t values(to_date('2008-2-1','yyyy-mm-dd'));
--
select
to_char(a,'yyyy')||'-'||to_number(to_char(a,'mm'))||'-'||to_number(to_char(a,'dd'))
from t
--
2008-2-1
----------
2008-02-27
----------
2008-2-1
select
to_char(sysdate,'yyyy') ||'-'||
to_char(to_number(to_char(sysdate,'mm'))) ||'-'||
to_char(to_number(to_char(sysdate,'dd')))
from dual
select to_char( to_date('20080101','YYYYMMDD ') ,'FMYYYY-MM-DD') from dual;