CODEWITHSUNDEEP
sql-server
Swazzy
Swazzy

Reputation: 97

How to Calculate age using a year month column?

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:

 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?

Upvotes: 2

Views: 121

Answers (4)

P.Salmon
P.Salmon

Reputation: 17590

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

Upvotes: 1

Mark
Mark

Reputation: 41

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

Upvotes: 1

Tim Lehner
Tim Lehner

Reputation: 15251

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:

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.

Upvotes: 1

gofr1
gofr1

Reputation: 15977

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

Upvotes: 1

Related Questions