Post reply

go back the past 12 months from today

  • June 6, 2012 at 8:20 am

    #270257

    I have a date stored in the table as 201003, how can I get the month and year of today and go back the past 12 months from the year month that is stored in the table?

  • June 6, 2012 at 8:33 am

    #1497664

    how can I get the month and year of today ...

    SELECT YEAR(GETDATE()), MONTH(GETDATE()), DATENAME(MONTH,GETDATE())

    ... and go back the past 12 months from the year month that is stored in the table?

    In your table you only have Year and Month portion. So you need to add day to make SQL use it as a date. As you not specified the datatype this value is stored in, I will assume the worse: it is integer (or other numeric):

    SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,-12,CAST(YourColumnName AS VARCHAR) + '01'),112) AS _12MonthAgo

    FROM YourTable

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • June 6, 2012 at 8:38 am

    #1497667

    its being stored as a number(6,0) such as 200109

  • June 6, 2012 at 8:43 am

    #1497669

    Here is an alternative method for converting a date stored as an integer in the format yyyymm:

    DECLARE @MyDate INT;

    SET @MyDate = 201003;

    SELECT DATEADD(mm, -12, DATEADD(mm, (@MyDate % 100) - 1, DATEADD(yy, (@MyDate / 100) - 1900, 0)));

  • June 6, 2012 at 8:45 am

    #1497670

    SQL_NuB (6/6/2012)

    its being stored as a number(6,0) such as 200109

    Made a slight change:

    DECLARE @MyDate DECIMAL(6,0);

    SET @MyDate = 201003;

    SELECT DATEADD(mm, -12, DATEADD(mm, (CAST(@MyDate AS INT) % 100) - 1, DATEADD(yy, (CAST(@MyDate AS INT) / 100) - 1900, 0)));

    SELECT DATEADD(mm, -12, DATEADD(mm, (@MyDate % 100) - 1, DATEADD(yy, (@MyDate / 100) - 1900, 0)));

    Both selects work.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply