oracle 如何在oracle中插入日期和时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7536628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to insert date and time in oracle?
提问by Deep
Im having trouble inserting a row in my table. Here is the insert statement and table creation. This is part of a uni assignment hence the simplicity, what am i doing wrong? Im using oracle SQL developer Version 3.0.04.'
我在我的表中插入一行时遇到问题。这是插入语句和表创建。这是 uni 作业的一部分，因此很简单，我做错了什么？我使用的是 oracle SQL 开发人员版本 3.0.04。
The problem i am having is that it is only inserting the dd/mon/yy but not the time. How do i get it to insert the time as well?
我遇到的问题是它只插入了 dd/mon/yy 而不是时间。我如何让它也插入时间？
INSERT INTO WORKON (STAFFNO,CAMPAIGNTITLE,DATETIME,HOURS)
VALUES ('102','Machanic Summer Savings',TO_DATE('22/April/2011 8:30:00AM','DD/MON/YY HH:MI:SSAM'),'3')
;
CREATE TABLE WorkOn
(
StaffNo NCHAR(4),
CampaignTitle VARCHAR(50),
DateTime DATE,
Hours VARCHAR(2)
)
;
Thanks for the help.
谢谢您的帮助。
EDIT: This is making no sense, i enter just a time in the field to test if time is working and it outputs a date WTF? This is really weird i may not use a date field and just enter the time in, i realise this will result in issues manipulating the data but this is making no sense...
编辑：这是没有意义的，我只在现场输入一个时间来测试时间是否有效并输出日期 WTF？这真的很奇怪，我可能不使用日期字段而只是输入时间，我意识到这会导致操作数据的问题，但这毫无意义......
采纳答案by Rob van Wijk
You are doing everything right by using a to_date function and specifying the time. The time is there in the database. The trouble is just that when you select a column of DATE datatype from the database, the default format mask doesn't show the time. If you issue a
通过使用 to_date 函数并指定时间，您所做的一切都是正确的。时间在数据库中。问题是当您从数据库中选择 DATE 数据类型的列时，默认格式掩码不显示时间。如果你发出一个
alter session set nls_date_format = 'dd/MON/yyyy hh24:mi:ss'
alter session set nls_date_format = 'dd/MON/yyyy hh24:mi:ss'
or something similar including a time component, you will see that the time successfully made it into the database.
或者类似的东西，包括时间组件，你会看到时间成功地进入了数据库。
回答by Sai prateek
You can use
您可以使用
insert into table_name
(date_field)
values
(TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));
Hope it helps.
希望能帮助到你。
回答by jschorr
Try this:
尝试这个：
...(to_date('2011/04/22 08:30:00', 'yyyy/mm/dd hh24:mi:ss'));
...(to_date('2011/04/22 08:30:00', 'yyyy/mm/dd hh24:mi:ss'));
回答by Codemaker
Just use TO_DATE()function to convert string to DATE.
只需使用TO_DATE()函数将字符串转换为DATE.
For Example:
例如：
create table Customer(
CustId int primary key,
CustName varchar(20),
DOB date);
insert into Customer values(1,'Vishnu', TO_DATE('1994/12/16 12:00:00', 'yyyy/mm/dd hh:mi:ss'));
回答by kingle
create table Customer(
CustId int primary key,
CustName varchar(20),
DOB date);
insert into Customer values(1,'kingle', TO_DATE('1994-12-16 12:00:00', 'yyyy-MM-dd hh:mi:ss'));
