none
Getting YYYYMM(format) integer field previous yr

    Question

  • Hello,

    I have an integer field  which stores dates. It has values such as 201401,201402 etc

    Create table #test(yrmnthid integer)

    insert into #test values ('201401')

    insert into #test values ('201402')

    select * from #test

    I want to get two things out of this field

    1. to get the previous year from the field

    so the answer would be (201301,201302)

    2. to get the e immediate preceding year and month from the field

    so the answer would be (201312,201401)

    please advice if this can be done in tsql?

    thanks

    Monday, July 14, 2014 10:42 AM

Answers

  • try this

    /*Create table test(yrmnthid integer);
    insert into test values ('201401');
    insert into test values ('201402');
    */
     With cte as(SELEct dateadd(year,-1,convert(datetime,convert(varchar(6),yrmnthid)+'01')) dt from test)
     select convert(varchar(4),Year(dt)) + right('0'+convert(varchar(2),month(dt)),2) from cte;
    
    
     With cte as(SELEct dateadd(Month,-1,convert(datetime,convert(varchar(6),yrmnthid)+'01')) dt from test)
     select convert(varchar(4),Year(dt)) + right('0'+convert(varchar(2),month(dt)),2) from cte;


    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, July 14, 2014 10:59 AM
  • ;with cte as 
    (
    SELECT yrcol , DATEDIFF(MONTH,0,CAST( CAST(D AS VARCHAR) + '01' AS DATE)) as DT
    FROM #test
    )

    select yrcol,CONVERT(VARCHAR(6),DATEADD(MONTH,DT-12,0) , 112) , CONVERT(VARCHAR(6),DATEADD(MONTH,DT-1,0),112)
    FROM Cte

    Thanks and regards, Rishabh K

    • Marked as answer by gans2014 Tuesday, July 15, 2014 9:41 PM
    Monday, July 14, 2014 11:02 AM

All replies

  • You can do as below

    SELECT yrmnthid,
    CONVERT(varchar(11),DATEADD(mm,((yrmnthid/100)-1900)*12 + ((yrmnthid % 100)-2),0),112) AS precedingyrmnthid
    FROM #test


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, July 14, 2014 10:53 AM
  • with cte
    as
    (
    select cast(cast(yrmnthid as varchar(6))+'01' as date) dt

    from #test
    ) select 
     convert(char(6),dateadd(year,-1,dt),112) dt1,
     convert(char(6),dateadd(y,-1,dt),112) dt2
     from cte

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 14, 2014 10:57 AM
    Answerer
  • try this

    /*Create table test(yrmnthid integer);
    insert into test values ('201401');
    insert into test values ('201402');
    */
     With cte as(SELEct dateadd(year,-1,convert(datetime,convert(varchar(6),yrmnthid)+'01')) dt from test)
     select convert(varchar(4),Year(dt)) + right('0'+convert(varchar(2),month(dt)),2) from cte;
    
    
     With cte as(SELEct dateadd(Month,-1,convert(datetime,convert(varchar(6),yrmnthid)+'01')) dt from test)
     select convert(varchar(4),Year(dt)) + right('0'+convert(varchar(2),month(dt)),2) from cte;


    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, July 14, 2014 10:59 AM
  • ;with cte as 
    (
    SELECT yrcol , DATEDIFF(MONTH,0,CAST( CAST(D AS VARCHAR) + '01' AS DATE)) as DT
    FROM #test
    )

    select yrcol,CONVERT(VARCHAR(6),DATEADD(MONTH,DT-12,0) , 112) , CONVERT(VARCHAR(6),DATEADD(MONTH,DT-1,0),112)
    FROM Cte

    Thanks and regards, Rishabh K

    • Marked as answer by gans2014 Tuesday, July 15, 2014 9:41 PM
    Monday, July 14, 2014 11:02 AM
  • You should work with date/datetime data the way it should be. When you insert int(numeric) data, you don't need to use single quotes. 
    Monday, July 14, 2014 3:30 PM
    Moderator