如何转换2004/05/20-12：00：00：000 成日期型（本身字符型）

select   to_char(to_date(EDI_ESTarr,'yyyy/mm/dd-hh24:mi:ss:ff3'),'IW')   as   weeknum,   count(act_shpmtno)   as   totalnum   from   T2AShipmentNo_KPI   where   t2ashipmentno_kpi.ACT_SHIPFROM_TYPE='BASIC'   and   t2ashipmentno_kpi.ACT_PROJECTTYPE='C'   and   to_date(t2ashipmentno_kpi.EDI_ESTarr,'yyyy/mm/dd-hh24:mi:ss:ff3')   between   to_date('2004-09-24','yyyy-mm-dd')   and   to_date('2004-10-26','yyyy-mm-dd')   group   by   to_char(to_date(EDI_ESTarr,'yyyy/mm/dd-hh24:mi:ss:ff3'),'IW')    
  这么转换总出错 问题点数：20、回复次数：6Top

1 楼ORARichard（没钱的日子......）回复于 2004-12-01 23:30:51 得分 0

:ff3去掉，edi_estarr的最后4位也去掉吧,  
  没有你这样的格式。Top

2 楼baojianjun（包子）回复于 2004-12-02 08:25:12 得分 2

to_date如何处理毫秒？  
  http://www.cnoug.org/viewthread.php?tid=18438Top

3 楼phant0m（YO-YO）回复于 2004-12-02 08:38:27 得分 0

9i下，为什么也不行呀？  
  SQL>   select   to_char(sysdate,'yyyy-mm-dd   hh24:mi:ssxff')   from   dual;  
   
  select   to_char(sysdate,'yyyy-mm-dd   hh24:mi:ssxff')   from   dual  
   
  ORA-01821:   日期格式无法识别Top

4 楼Tadpole0510（学习、学习）回复于 2004-12-02 08:42:51 得分 0

我的是8i的  
  字段存的就是2004/11/26-08:39:00:000   是var2  
  to:   ORARichard(没钱的日子好难过啊)    
   
    select   to_char(to_date(EDI_ESTarr,'yyyy/mm/dd-hh24:mi:ss'),'IW')   as   weeknum,   count(act_shpmtno)   as   totalnum   from   T2AShipmentNo_KPI   where   t2ashipmentno_kpi.ACT_SHIPFROM_TYPE='BASIC'   and   t2ashipmentno_kpi.ACT_PROJECTTYPE='C'   and   to_date(t2ashipmentno_kpi.EDI_ESTarr,'yyyy/mm/dd-hh24:mi:ss')   between   to_date('2004-09-24','yyyy-mm-dd')   and   to_date('2004-10-26','yyyy-mm-dd')   group   by   to_char(to_date(EDI_ESTarr,'yyyy/mm/dd-hh24:mi:ss'),'IW')    
  出现"日期格式图片在转换整个字符之前结束"   错误  
   
   
       
  Top

5 楼ORARichard（没钱的日子......）回复于 2004-12-02 08:52:17 得分 18

select   to_char(to_date(substr(EDI_ESTarr,1,19),'yyyy/mm/dd-hh24:mi:ss'),'IW')   as   weeknum,   count(act_shpmtno)   as   totalnum   from   T2AShipmentNo_KPI   where   t2ashipmentno_kpi.ACT_SHIPFROM_TYPE='BASIC'   and   t2ashipmentno_kpi.ACT_PROJECTTYPE='C'   and   to_date(substr(t2ashipmentno_kpi.EDI_ESTarr,1,19),'yyyy/mm/dd-hh24:mi:ss')   between   to_date('2004-09-24','yyyy-mm-dd')   and   to_date('2004-10-26','yyyy-mm-dd')   group   by   to_char(to_date(substr(EDI_ESTarr,1,19),'yyyy/mm/dd-hh24:mi:ss'),'IW')Top

6 楼Tadpole0510（学习、学习）回复于 2004-12-02 09:15:08 得分 0

to:   ORARichard(没钱的日子好难过啊)    
  每次你都救我，真是感谢。Top

相关问题

• 转换
• date
• basic
• t2ashipmentno
• kpi
• estarr
• yyyy
• hh24
• projecttype
• shpmtno

• 帖主：Tadpole0510
• baojianjun
• ORARichard

• Oracle类图书