Solved

SQL substring statement

Posted on 2012-03-26
11
367 Views
Last Modified: 2012-04-12
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
0
Comment
Question by:zintech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 13

Expert Comment

by:Ashok
ID: 37767525
What is Data Type for PeriodStart?
0
 

Author Comment

by:zintech
ID: 37767527
It is integer data type
0
 
LVL 7

Expert Comment

by:designatedinitializer
ID: 37767559
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.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 13

Expert Comment

by:Ashok
ID: 37767591
BTW, you cannot use SubString directly on integer.....

select * from Table1
where
CAST(Substring(CAST(PeriodStart as varchar(100)), 5,2) AS INT)  > 12
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37767618
Just as designatedinitializer has mentioned,
you should be using Date as Data Type for this field.

Ashok
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37767630
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

0
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37767634
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37768423
List 5 input records and expected corresponding output.
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37768509
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

0
 
LVL 30

Expert Comment

by:hnasr
ID: 37768586
Try this for table a and field periodstart.

UPDATE a
      SET periodstart =
            case WHEN substring(cast(periodstart as nvarchar(8)),7,2) > '12' THEN periodstart + 10000
            Else periodstart
END
0
 
LVL 9

Accepted Solution

by:
keyu earned 500 total points
ID: 37770961
UPDATE table1 SET PeriodStart = convert(varchar,(convert(left(periodstart,4),int) + 1))+right(periodstart,length(periodstart)-4) WHERE right(periodstart,2)>12

DEscription:

* 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

 *right(periodstart,length(periodstart)-4)

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

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

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question