Updating the year?

Question

Using sql server 2000

Date Column datatype is 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 
.....

From the above table, from January 26 to February 03, the year was displaying wrong for all the id's

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

Expected Ouput

ID Date

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

Query like

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

How to make a query of updating the values.

Need query Help

Answer 1

If the column is of type DATETIME have a look at DATEADD

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

For the VARCHAR you could 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

If the column is defined as varchar:

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

If the column is defined as 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'}

Notes:

• Although the {d'yyyy-mm-dd'} syntax is not legal T-SQL, it is nevertheless intercepted and interpreted correctly by any SQL Server client stack, e.g. ODBC, OLEDB, ADO.NET, etc. This also applies to the interactive querying tools. If you don't like using ODBC escape sequences, you can use convert(datetime, 'yyyy-mm-dd', 120) instead.
• convert(datetime, 'yyyy-mm-dd', 120) applies the ODBC standard datetime format, which is culture/locale invariant, and therefore very useful for constructing datetime strings regardless of the current server locale.

Answer 4

A better approach, avoids replacing september 20

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