更新年份？

Question

使用sql server 2000

日期列数据类型为varchar

Table1

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日到2003年2月，所有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

Answer 2

UPDATE Table1 
SET Date = Replace(Date, '2009', '2010') 
WHERE 
    DATE LIKE '2009%' 
AND
    convert(DATETIME, Date, 112) BETWEEN '2009-01-26 00:00:00' AND 
                                         '2009-02-03 23:59:59.999'

Answer 3

一种更好的方法，避免替换9月20日

UPDATE Table1 Set Date = Replace(Date, '2009', '2010') WHERE DATE LIKE '2009%'