Post reply

Subtract Year from Year and Month

  • April 10, 2018 at 1:31 pm

    #344591

    subtract 1 year from the year and month.

    I have @yearmonth='2017-06' . I want another variable to store 1 year before value from the @Yearmonth. eg-
    i have data -'2017-06' and i want to have '2016-06' in another variable.
    please help. Thanks.

  • April 10, 2018 at 1:47 pm

    #1986224

    There may be a better way, but here you go:
    DECLARE @yearMonth CHAR(7);
    DECLARE @prioryearMonth CHAR(7);

    SET @yearMonth = '2017-06';

    SET @prioryearMonth = CONCAT(CAST(LEFT(@yearMonth, 4) AS Int) -1, RIGHT(@yearMonth, 3));

    SELECT @yearMonth AS yearMonth, @prioryearMonth AS prioryearMonth

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • April 10, 2018 at 5:07 pm

    #1986236

    Try:
    DECLARE @yearmonth char(7)='2017-01', @YmMinusYr char(7);
    SELECT @YmMinusYr = Convert(char(7), DateAdd(yy, -1, Cast(@yearmonth+'-01' AS date)), 120);
    SELECT @yearmonth ThisYear, @YmMinusYr Last year;

  • April 10, 2018 at 6:11 pm

    #1986244

    Thank you both. it worked.

  • April 11, 2018 at 12:38 am

    #1986266

    This can be simplified to be done in the variable declaration statement.
    😎

    DECLARE @YEARMONTH  CHAR(7) = '2017-06';
    DECLARE @PRIORYEARMONTH CHAR(7) = CONVERT(CHAR(7),DATEADD(YEAR,-1,CONVERT(DATE,CONCAT(@YEARMONTH,'-01'),120)),120);

    Or even simpler

    DECLARE @YEARMONTH  CHAR(7) = '2017-06';
    DECLARE @PRIORYEARMONTH CHAR(7) = CONCAT(CONVERT(INT,LEFT(@YEARMONTH,4),0) - 1,RIGHT(@YEARMONTH,3));

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

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