如何计算年龄使用一个月的专栏?

I am tryign to get the age of a product by using a YRMO (Year/Month Ex:201606) column. I want to be able to have the product, Yrmo and age so:

我试图通过使用YRMO(年/月例:201606)来了解产品的年龄。我想要有产品，Yrmo和年龄:

 Product  | YrMo   | Age|
 A        | 201602 |  1 |
 A        | 201603 |  2 |
 B        | 201605 |  4 |

I used this method but wasn't able to get the results

我用了这种方法，但没有得到结果

 SELECT 
       Product      
     , YrMo
     , [Month_#]    =   DATEDIFF(MONTH,201601,YrMo) 

 FROM Table

When I used a datetype it returned consecutive months:

当我使用日期类型时，它会连续返回几个月:

  [Month_#] =   DATEDIFF(MONTH,'1900-01-01',YrMo)

Instead.. Any tips on how to be able to get around this?

而不是. .关于如何避开这个问题有什么建议吗?

4 个解决方案

#1

Simple

简单的

SELECT  Product,
        YrMo,
        DATEDIFF(month,STUFF(YrMo,5,0,'-')+'-01','2016-01-01') as Age
FROM YourTable

Output:

输出:

Product YrMo    Age
A       201602  1
A       201603  2
B       201605  4

#2

You are close with your query, but you can't just cast an int or 6-digit string to a date. Here's your query, modified:

您的查询非常接近，但是您不能将一个整数或6位字符串转换为日期。这是你的查询、修改:

select 
    Product      
    , YrMo
    , [Month_#] = datediff(month, cast(cast(201601 as varchar(11)) + '01' as date), cast(cast(YrMo as varchar(11)) + '01' as date))
from Products

Results:

结果:

Product  YrMo    Month_#
A        201602  1
A        201603  2
B        201605  4

Note that I'm casting ints to varchar and then appending '01' to make it a full date. Depending on the datatypes you're really using, you can probably make this prettier.

注意，我将ints插入varchar，然后添加'01'以使它成为一个完整的日期。根据您实际使用的数据类型，您可以使它更漂亮。

#3

SELECT product, 
       yrmo, 
       [Month_#] = Datediff(month, Cast('1900-01-01' AS DATE), Cast( 
                   '20160101' AS DATE) 
                   ) 
FROM   table

#4

DECLARE @T TABLE (Product VARCHAR(1), YrMo INT,  Age INT)
INSERT INTO @T VALUES
( 'A',         201602 ,  1) ,
( 'A' ,        201603 ,  2),
( 'B'  ,       201605 ,  4)


    SELECT      AGE, YRMO, 
                (YRMO / 100 * 12 +  YRMO % 100 ) - (201601 /100 *12 + 201601 % 100) AS  AGE
    FROM    @T

#1