Update of the year?

Question

Using SQL Server 2000

Date Column Type: varchar

Table 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 
.....

From the table above, from January 26 to February 03, the year was displayed incorrectly for all identifiers

I want to update only the year 2010 instead of 2009 as 20100127 instead of 20000127

Expected Conclusion

ID Date

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

Type request

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

How to make a request for updating values.

Help required on request

Answer 1

If the column is of type DATETIME, see DATEADD

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

For VARCHAR you can try something like

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

OR something like

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

, 20

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

Answer 4

varchar:

update Table1
set [Date] = '2010' + substring([Date], 5, 4)
where [Date] between '20090126' and '20090203'

datetime:

update Table1
set [Date] = convert(datetime, '2010-' + substring([Date], 5, 2) + '-' + substring([Date], 7, 2), 120)
where [Date] between {d'2009-01-26'} and {d'2009-02-03'}

:

• {d'yyyy-mm-dd'} T-SQL, SQL Server, . ODBC, OLEDB, ADO.NET .. . escape- ODBC, convert(datetime, 'yyyy-mm-dd', 120) .
• convert(datetime, 'yyyy-mm-dd', 120) ODBC, / .