update test set birthday=to_date(sysdate,'yyyy-mm-dd') where test_id=100;
birthday是date类型,原值2008-10-16 上午 11:35:40报错:ora-01861 literal does not match format string
birthday是date类型,原值2008-10-16 上午 11:35:40报错:ora-01861 literal does not match format string
直接update test set birthday = sysdate where test_id = 100;
不过不管怎么样date类型是到毫秒的
所以就算你用trunc(sysdate)也只是置成当天的0点0分0秒而已
除非你不用date 类型的
那我怎么修改表里的值呢
update test set birthday=to_date('2008-10-16 11:35:40','yyyy-mm-dd') where test_id=100;
也会报错
2. use date type to store, but use to_char to display your date format with char string.
select to_char(datefield,'yyyy-mm-dd') from tablename;
3. if you just want to store date not include time in date field, do like the following:
update test set birthday=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') where test_id=100;
2. use date type to store, but use to_char to display your date format with char string.
select to_char(datefield,'yyyy-mm-dd') from tablename;
3. if you just want to store date not include time in date field, do like the following:
update test set birthday=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') where test_id=100;
2. use date type to store, but use to_char to display your date format with char string.
select to_char(datefield,'yyyy-mm-dd') from tablename;
3. if you just want to store date not include time in date field, do like the following:
update test set birthday=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') where test_id=100;
update test set birthday=to_date('2008-10-16 11:35:40','yyyy-mm-dd hh24:ss') where test_id=100;
看来我是不能一句话解决这个问题
麻烦了一下,用个小过程解决了declare
old_date varchar2(20);
begin
select to_char(birthday,'yyyy-mm-dd') into old_date from test where test_id=100;
update test set birthday=to_date(old_date,'yyyy-mm-dd') where test_id=100;
commit;
end;