sql-server - 更新年份？

Question

使用 SQL Server 2000

日期列的数据类型是varchar

表1

ID Date 

01 20100122
01 20100123
01 20100124
01 20100125
01 20100126
01 20090127
01 20090128
01 20090129
01 20090130
01 20090131
01 20090201
01 20100202
01 20090203
01 20100204
01 20100205
02 
.....

从上表来看，从1月26日到2月3日，所有id的年份都显示错误

我只想更新年份 2010 而不是 2009 就像 20100127 而不是 20000127

预期输出

ID Date

01 20100126
01 20100127
...
01 20100203
...
02

查询喜欢

update table1 set 2010 instead of 2009 where date between 20090126 and 20090203

如何查询更新值。

需要查询帮助

Answer 1

如果该列是 DATETIME 类型，请查看 DATEADD

SELECT GETDATE(),  DATEADD(year, 1, GETDATE())

对于 VARCHAR 你可以尝试类似的东西

DECLARE @Table TABLE(
        Date VARCHAR(8)
)

INSERT INTO @Table SELECT '20090301'

SELECT  *
FROM    @Table

UPDATE  @Table
SET     Date = '2010' + RIGHT(Date, 4)
WHERE   LEFT(Date,4) = '2009'

SELECT *
FROM    @Table

或者类似的东西

DECLARE @Table TABLE(
        ID VARCHAR(4),
        Date VARCHAR(8)
)

INSERT INTO @Table SELECT '01','20100122'
INSERT INTO @Table SELECT '01','20100123' 
INSERT INTO @Table SELECT '01','20100124' 
INSERT INTO @Table SELECT '01','20100125' 
INSERT INTO @Table SELECT '01','20100126' 
INSERT INTO @Table SELECT '01','20090127' 
INSERT INTO @Table SELECT '01','20090128' 
INSERT INTO @Table SELECT '01','20090129' 
INSERT INTO @Table SELECT '01','20090130' 
INSERT INTO @Table SELECT '01','20090131' 
INSERT INTO @Table SELECT '01','20090201' 
INSERT INTO @Table SELECT '01','20100202' 
INSERT INTO @Table SELECT '01','20090203' 
INSERT INTO @Table SELECT '01','20100204' 
INSERT INTO @Table SELECT '01','20100205' 

UPDATE  @Table
SET     Date = '2010' + RIGHT(Date, 4)
WHERE   Date >= '20090126'
AND     Date <= '20090203'
AND     ID = '01'

SELECT  *
FROM    @Table