SQL substring statement

What I need to do is to do an UPDATE SQL statement where I add one to the first 4 digits of a field, the year, if the last 2 digits of the field, the month, is greater than 12.  I can't figure out how to do this.  This is what I have right now but it doesn't work.

UPDATE table1 SET SUBSTRING(PeriodStart, 1, 4) = SUBSTRING(PeriodStart, 1, 4) + 1 WHERE SUBSTRING(PeriodStart, 5, 2) > 12
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AshokSr. Software EngineerCommented:
What is Data Type for PeriodStart?
zintechAuthor Commented:
It is integer data type
I'm sorry to break it to you like this, but you're doing it all wrong.
You should store dates as DATE datatype. And then you can use MONTH(), DAY()... and a whole bunch of other date functions to do whatever date computations you need.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

AshokSr. Software EngineerCommented:
BTW, you cannot use SubString directly on integer.....

select * from Table1
CAST(Substring(CAST(PeriodStart as varchar(100)), 5,2) AS INT)  > 12
AshokSr. Software EngineerCommented:
Just as designatedinitializer has mentioned,
you should be using Date as Data Type for this field.

As said using a date field would be a lot better but below should help.

You don't need substring to do what you need try this:

update table1
set periodstart = convert(int,convert(varchar,left(periodstart,4)+1) + convert(varchar,right(periodstart,2)))
where left(periodstart,4)>12

Open in new window

Patrick TallaricoDecision Support and Systems AnalystCommented:
I agree that you should probably store the dates as dates... but if you are storing them as integers, you should just be able to add a number to the value.  Substring will not work with the integer data type.  It looks like you are trying to cycle through the year, and add the one to the year value when the month value exceeds 12.  Is this correct.

If your date was stored like 20110102 for 01-02-2011  then you should be able to add 10000 to the integer to increase the year value.  Obviously the number would need to be adjusted to whatever format you are using.
Hamed NasrRetired IT ProfessionalCommented:
List 5 input records and expected corresponding output.
Sorry my SQL should have been this instead:
update table1
set periodstart = convert(int,convert(varchar,left(periodstart,4)+1) + convert(varchar,right(periodstart,2)))
where right(periodstart,2)>12

Open in new window

Hamed NasrRetired IT ProfessionalCommented:
Try this for table a and field periodstart.

      SET periodstart =
            case WHEN substring(cast(periodstart as nvarchar(8)),7,2) > '12' THEN periodstart + 10000
            Else periodstart
UPDATE table1 SET PeriodStart = convert(varchar,(convert(left(periodstart,4),int) + 1))+right(periodstart,length(periodstart)-4) WHERE right(periodstart,2)>12


* convert(varchar,(convert(left(periodstart,4),int) + 1)) =>

-> It will retrieve first 4 characters convert it into int
-> add 1 to this
->  again covert it back to varchar after addition


- > this will find remaining charaters of field    "periodstart"

* after this we are concating both sstring with '+' character

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.