Year update in table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Year update in table

Author

Topic

Raveen
Starting Member

1 Post

Posted - 2011-05-10 : 04:32:26

Hi,

Can someone help me to update the year in table. The table name is ProductConsumption and column name is ConsumptionDate in which i have multiple rows if different date but only with year 2009, for ex:15/01/2009 and so on. I need help how to update only a year here from 2009 to 2010.

Can someone please help me, its SQL Server 2005

Thanks,
Raveen

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-10 : 05:28:21

What is your ConsumptionDate datatype?

In Love... With Me!

Serrano88
Starting Member

4 Posts

Posted - 2011-05-10 : 15:41:35

Assuming that your datatype is datetime, you can use this update script:

UPDATE ProductConsumption
SET ConsumptionDate = DATEADD(YYYY,1,ConsumptionDate)
WHERE DATEPART(Year,consumptiondate) = '2009'

You might want to test the results before you update to ensure that the functions work properly on a European date format:

SELECT ConsumptionDate, DATEADD(YYYY,1,ConsumptionDate)
FROM ProductConsumption
WHERE DATEPART(Year,consumptiondate) = '2009'

If ConsumptionDate is a varchar datatype, the date functions will not work. It is best practice to store date data in datetime data types. However, you could update a varchar field like this:

UPDATE ProductConsumption
SET ConsumptionDate = substring(ConsumptionDate,1,6)+'2010'
WHERE substring(ConsumptionDate,7,4) = '2009'

___________________________________________________________
For SQL Contractor and Consulting Support:
http://www.thedatasolutioncenter.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources