CODEWITHSUNDEEP
sqlsql-server
Anabella5c8d
Anabella5c8d

Reputation: 23

Changing year only in date in column with case statement

I have a table with end dates in different years and would like to change any year ending in '2004' to '4712'. The column is Date type and I keep getting an error

I have tried different codes and case statements but none of which worked

select
    case
        when h.EndDate like '2004%' then '4712%'
        else h.EndDate
    end as EndDate
from table h

1/31/2004
2/28/2004
3/31/2004

and so on would be the only years changed to:

1/31/4712
2/28/4712
3/31/4712

Upvotes: 2

Views: 664

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Personally, I would use datefromparts():

select datefromparts(4712, month(h.enddate), day(h.enddate))
from h
where h.enddate >= '2014-01-01' and h.enddate < '2015-01-01';

In an update, this looks like:

update h
    set enddate = datefromparts(4712, month(h.enddate), day(h.enddate))
    where h.enddate >= '2014-01-01' and h.enddate < '2015-01-01';

Upvotes: 1

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

You can do this using dateadd(). Like works with string data type like varchar, nvarchar etc. When working with dates you need to use functions prepared to works with this data types (date, datetime etc.) To your date I added 2708 year which added to 4712.

update table
set EndDate = dateadd(year, 2708, EndDate)
where year(EndDate) = 2004

Upvotes: 1

Related Questions