Addohm Addohm - 2 months ago 7
SQL Question

SQL Modify Column Data

I need to go through an entire column of datetime data and modify the year so that they're all 2016. I have a customer that decided to modify my code and it jacked up the math I wrote that calculates the current date and time. So now, they see years 2016, 2116, 2216, etc. Anyway, what I am looking for is some script that will go through a column and if the last four digits of the column aren't 2016, replace the last four digits with 2016 while the beginning data remains intact.

I.e. If the date is xx/xx/2116, change it to xx/xx/2016.

I am unfortunately not well practiced in SQL for a quick fix on this one so I turn to you pros for help! :D

Thanks in advance.

Answer

Use STUFF:

UPDATE dbo.YourTable
SET SomeColumn = STUFF(SomeColumn,LEN(SomeColumn)-3,4,'2016')
WHERE RIGHT(SomeColumn,4) <> '2016';
Comments