Adding integer strings with zero values

Question

0.00/5 (No votes)

See more:

C#

Hi there.

I am trying to add strings which are integers. I have 201404 as input and I need it to be converted to 201503 so the only way to do this is to increase the year (2014) by 1 and decrease the month 02 by 1. 
I have tried the below but the leading zero in the month does not seem to preserve

C#

declare
     @YearMonth int =201404,
     @left int = 0,
     @right int= 0

 SET @YearMonth= CAST(@YearMonth AS VARCHAR(6))
  set @left = cast(left(@YearMonth,4) +1   as varchar(max))
  set @right = right(@YearMonth,2) - 1
  set @right = cast(@right as varchar(2))

 SET @right = RIGHT(('0' + Cast(@right as varchar(2))),2)

 print @left

 print RIGHT( '0' + LTRIM( RTRIM( @right ) ), 6 )

Posted 20-Nov-15 1:04am

Miss R

Updated 20-Nov-15 2:25am

Patrice T

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-11-20T01:30:00

Change your DB.
Don't store dates (even partial dates) as strings: always store them as DATE or DATETIME - that way, you can be sure they are all valid dates, and use the built in functions such as DATEPART and DATEADD. In you case, just set the day part to the first of teh month, and the time to midnight.

Storing any numeric data as strings always gives a lot more problems than it solves in the long run.

Patrice T · Answer 2 · 2015-11-20T02:21:00

Solution 3

Why do you convert to string at all ?

SQL

declare
    @YearMonth int =201404,
set @YearMonth= @YearMonth + 99
print @YearMonth

Posted 20-Nov-15 2:21am

Patrice T

Updated 20-Nov-15 2:23am

v2

Wendell D H · Answer 3 · 2015-11-20T01:43:00

I believe the problem is because you are treating integers like strings.
03 is not a valid integer so it gets converted to 3.
The rest of the convertions from int to varchar and back is the server helping you out a lot.

However, with what you have you can get to the final value you are looking for.

This should work...

SQL

print cast(@left as varchar(4)) + replicate('0', 2-len(@right)) + cast(@right as varchar(2))

Adding integer strings with zero values

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0