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

问题描述

我正在尝试添加整数字符串。我有201404作为输入，我需要将其转换为201503，所以唯一的方法是将年份（2014）增加1，将月份02减少1。
我已经尝试了以下方法，但是

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)

推荐答案

使用YYYYMM整数格式可以加减法比较困难。一种方法是转换为数月，然后转换回格式。因此，这会将值转换为月数

Dealing with integer YYYYMM format can be difficult when adding and subtracting months. One method is to convert to a number of months, and then convert back to the format. So, this converts the value to a number of months

select (@YearMonth / 100) * 12 + (@YearMonth % 100)

然后我们可以添加一个数字，例如11并转换回整数格式：

Then we can add a number, such as 11 and convert back to the integer format:

select (( (@YearMonth / 100) * 12 + (@YearMonth % 100) + 11) / 12) * 100 +
        ( (@YearMonth / 100) * 12 + (@YearMonth % 100) + 11) % 12)
       ) as yyyymm

另一种可能更简单的方法是使用日期算术：

Another method that might be simpler is to use date arithmetic:

select dateadd(11, month, cast(@YearMonth as varchar(255)) + '01')

此返回一个约会。您可以将其转换为以下数字：

This returns a date. You can convert it back to the number as:

select (year(dateadd(11, month, cast(@YearMonth as varchar(255)) + '01')) * 100 +
        month(dateadd(11, month, cast(@YearMonth as varchar(255)) + '01'))
       ) as yyyymm

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