To_date问题

Question

我想把如下的sql的日期（已经导入到excel表）导入到orale中的date2008-06-20 21:05:14.957 2006-11-23 14:49:15.92 2009-12-31 00:00:00.0
2008-07-28 20:06:21.257 2006-12-23 09:50:14.56 2008-08-28 00:00:00.0
2008-08-07 22:37:22.567 2006-10-06 10:51:39.497 2009-12-31 00:00:00.0
2008-08-10 20:28:54.883 2006-10-19 14:13:39.087 2009-12-31 00:00:00.0
2008-06-22 21:37:25.71 2007-05-04 18:16:40.94 2008-12-31 00:00:00.0
2008-06-10 14:05:50.893 2006-11-02 21:11:02.143 2008-06-23 00:00:00.0
2008-06-07 01:18:38.7 2006-11-09 20:06:41.923 2008-06-11 00:00:00.0
2008-06-20 20:52:00.913 2006-11-24 08:37:09.28 2009-03-31 00:00:00.0
2008-08-03 20:15:17.547 2006-11-23 15:37:24.31 2009-12-31 00:00:00.0
2008-07-28 21:23:52.25 2006-11-24 17:17:56.95 2009-12-31 00:00:00.0
2008-08-05 20:53:37.677 2006-10-20 22:28:06.803 2009-12-31 00:00:00.0
2008-06-21 01:27:27.02 2006-11-21 23:47:35.19 2009-12-31 00:00:00.0
2008-06-23 17:14:33.027 2008-06-23 17:00:40.907 2009-12-31 00:00:00.0
insert into CONTRACTINFO (ContractID,ContractName,DestinationMarketID,UserID,FirstMakeUserID,CurrencyID,AirlineID,UserID_LastUper,ContractType,BranchARCNo,FareType,ContractNo,InService,LastUpdateDate,MakeDate,TicketBy,ExpiringNotify,TravelClassID,FareTypeSelected,ReleaseFlag,CodeShare,CanBook,CanCall,OnlineOnly) VALUES('0E0E6C60-95F7-4E5A-81DA-0037C10AB253','2008/09 REV 19 MAY EUROPE CONSOLIDATOR DCSAR7/ICSAR7','7234EF93-F4EA-4C19-B091-6746AA4772BB','7D15BD36-1E8D-4115-B2C1-246674F260CB','0ECE1351-0EDC-49A0-A6EB-9795E1E084EB','C7D20C25-C148-4D6C-B9B5-2469FA253746','48355049-D7C4-4E64-863C-AC1FDCE403F9','7D15BD36-1E8D-4115-B2C1-246674F260CB','AIR','','ATPCO','',true,TO_DATE('2008-06-20 21:05:14.957',''),TO_DATE('2006-11-23 14:49:15.92',''),TO_DATE('2009-12-31 00:00:00.0',''),0,'B400FB4C-6C08-42B3-87CE-A0C759F1A18B','Bulk',true,'AMERICAN EAGLE,BA',true,false,false);这个To_date该如何写呢？

Answer 1

select TO_DATE('2008-06-20 21:05:14.957','') from dual''这个怎么来写

Answer 2

date类型是无法存入毫秒的
要用timestamp类型
例子
drop table a;
create table a(dtdate timestamp);
insert into a values(to_timestamp('2002-03-20 13:40:05.43','yyyy-mm-dd hh24:mi:ss.ff'));
commit;
select * from a;
结果
1 20-3月 -02 01.40.05.430000 下午

Answer 3

就算你用to_timestamp('2002-03-20 13:40:05.43','yyyy-mm-dd hh24:mi:ss.ff')
这种方式将毫秒插入是没有错误的.
但查询时你会发现是没有毫秒的.

Answer 4

如果你原来的表不想重新建的话只需要执行
alter table a modify(dtdate timestamp);
即可

Answer 5

在excel中把日期整理成像 2008-06-20 21:05:14的格式，在引用to_date('2008-06-20 21:05:14','yyyy-mm-dd hh24:mi:ss')excel中整理日期数据时可以使用类似下面的函数得到： =LEFT(A1,19)

Answer 6

to：dbcxbj and oracledbalgtu谢谢，我再试试吧