关于一个日期格式字段的更新～～

Question

我想更新一个日期格式的字段的年份，
例如：
son表
s_id     s_name    s_born                     f_id
1 儿子1 1983-11-13 00:00:00.000 1
2 女儿2 1900-01-29 00:00:00.000 2
3 儿子3 1984-01-13 00:00:00.000 1
4 儿子4 1984-01-13 00:00:00.000 2
5 儿子5 1984-01-13 00:00:00.000 3
father表
f_id     f_name   f_born
1 张三 1967-11-13 00:00:00.000
2 李四 1954-11-13 00:00:00.000
3 王五 1924-11-13 00:00:00.000把son表中的s_born字段中，以1984开头的全部改为1985，语句改怎么写？

Answer 1

s_id     s_name    s_born                     f_id
1 儿子1 1983-11-13 00:00:00.000 1
2 女儿2 1900-01-29 00:00:00.000 2
3 儿子3 1984-01-13 00:00:00.000 1
4 儿子4 1984-01-13 00:00:00.000 2
5 儿子5 1984-01-13 00:00:00.000 3update son
set s_born = '1985' + substring(convert(varchar(10) , s_born , 120),6,5)
where year(s_born) = 1984

Answer 2

update son
set s_born = '1985' + substring(convert(varchar(10) , s_born , 120),5,6)
where year(s_born) = 1984

Answer 3

update son set s_born = dateadd(yyyy,1,s_born) where year(s_born) = 1984

Answer 4

CREATE TABLE son(s_id INT,s_name NVARCHAR(20),s_born DATETIME,f_id INT)
INSERT SON
SELECT 1,'儿子1','1983-11-13 00:00:00.000',1 UNION ALL
SELECT 2,'女儿2','1900-01-29 00:00:00.000',2 UNION ALL
SELECT 3,'儿子3','1984-01-13 00:00:00.000',1 UNION ALL
SELECT 4,'儿子4','1984-01-13 00:00:00.000',2 UNION ALL
SELECT 5,'儿子5','1984-01-13 00:00:00.000',3
SELECT * FROM SONUPDATE SON SET S_BORN=REPLACE(S_BORN,'1984','1985') WHERE YEAR(S_BORN)=1984SELECT * FROM SONDROP TABLE SON

Answer 5

create table son(s_id int,    s_name varchar(10), s_born datetime,  f_id int)
insert son
select 1, '儿子1','1983-11-13 00:00:00.000', 1 union all
select 2, '女儿2', '1900-01-29 00:00:00.000', 2 union all
select 3, '儿子3', '1984-01-13 00:00:00.000', 1 union all
select 4, '儿子4', '1984-01-13 00:00:00.000', 2 union all
select 5, '儿子5', '1984-01-13 00:00:00.000', 3 
select * from son where convert(varchar(4),s_born,112)='1984'
update son
set s_born=dateadd(year,1,s_born)
where convert(varchar(4),s_born,112)='1984'
s_id        s_name     s_born                                                 f_id        
----------- ---------- ------------------------------------------------------ ----------- 
1           儿子1        1985-11-13 00:00:00.000                                1
2           女儿2        1900-01-29 00:00:00.000                                2
3           儿子3        1985-01-13 00:00:00.000                                1
4           儿子4        1985-01-13 00:00:00.000                                2
5           儿子5        1985-01-13 00:00:00.000                                3（所影响的行数为 5 行）--drop table son

Answer 6

create table son(s_id int,    s_name varchar(10), s_born datetime,  f_id int)
insert son
select 1, '儿子1','1983-11-13 00:00:00.000', 1 union all
select 2, '女儿2', '1900-01-29 00:00:00.000', 2 union all
select 3, '儿子3', '1984-01-13 00:00:00.000', 1 union all
select 4, '儿子4', '1984-01-13 00:00:00.000', 2 union all
select 5, '儿子5', '1984-01-13 00:00:00.000', 3 
select * from son where convert(varchar(4),s_born,112)='1984'
update son
set s_born=dateadd(year,1,s_born)
where convert(varchar(4),s_born,112)='1984'
--drop table son
s_id        s_name     s_born                                                 f_id        
----------- ---------- ------------------------------------------------------ ----------- 
1           儿子1        1983-11-13 00:00:00.000                                1
2           女儿2        1900-01-29 00:00:00.000                                2
3           儿子3        1985-01-13 00:00:00.000                                1
4           儿子4        1985-01-13 00:00:00.000                                2
5           儿子5        1985-01-13 00:00:00.000                                3（所影响的行数为 5 行）上一结果测试时更新了两次,这个就是效果

Answer 7

楼主主要是定义条件
where datepart(yy,s_born)=1984可以
where year(s_born)都行
用between也可以

Answer 8

UPDATE SON SET S_BORN=REPLACE(S_BORN,'1984','1985') WHERE YEAR(S_BORN)=1984

Answer 9

更新方法用 stuff(s_born,1,4,'1985')也行
只要定义好条件