zintech
asked on
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
UPDATE table1 SET SUBSTRING(PeriodStart, 1, 4) = SUBSTRING(PeriodStart, 1, 4) + 1 WHERE SUBSTRING(PeriodStart, 5, 2) > 12
What is Data Type for PeriodStart?
ASKER
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.
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.
BTW, you cannot use SubString directly on integer.....
select * from Table1
where
CAST(Substring(CAST(Period Start as varchar(100)), 5,2) AS INT) > 12
select * from Table1
where
CAST(Substring(CAST(Period
Just as designatedinitializer has mentioned,
you should be using Date as Data Type for this field.
Ashok
you should be using Date as Data Type for this field.
Ashok
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:
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
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.
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.
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
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
UPDATE a
SET periodstart =
case WHEN substring(cast(periodstart
Else periodstart
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.