添加零值的整数字符串 [英] Adding integer strings with zero values

问题描述

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

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 )

推荐答案

更改数据库。

不要将日期（甚至部分日期）存储为字符串：始终将它们存储为DATE或DATETIME - 这样，您可以确定它们都是有效的日期，并使用内置函数，如DATEPART和DATEADD。在你的情况下，只需将日期部分设置为第一个月，以及将时间设置为午夜。



将任何数字数据存储为字符串总会产生更多问题比起长期解决的问题。

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.

为什么要转换为字符串？

Why do you convert to string at all ?

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

我认为问题是因为你正在对整数这样的字符串进行处理。

03不是一个有效的整数，所以它被转换为3.

剩下的从int到varchar和back的转换是帮助你解决很多问题的服务器。



然而，凭借你所拥有的，你可以获得你想要的最终价值。



这应该有效...

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...

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

这篇关于添加零值的整数字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！