conversion of a varchar data type to a datetime data type resulted in an out-of-range value

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 6, 2018 at 10:10 am

  #376354

  So my query looks like this below. When i put @month as 11 it works fine and gives 2019-01. but it does not work when i do @month=12 and show the out of range error. can someone help with same. I am trying to get output as-2019-02.

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(2018 as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)  
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(2018 as varchar)+'-'+case when 11 <10 then replicate('0',1)+cast(11 as varchar)
else cast(11+1 as varchar) end + '-01'),120)  --output-2019-01

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(2018 as varchar)+'-'+case when 12 <10 then replicate('0',1)+cast(12 as varchar) 
else cast(12+1 as varchar) end + '-01'),120)  --Error

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  November 6, 2018 at 10:30 am

  #2012580

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  November 6, 2018 at 10:30 am

  #2012581

  Papil - Tuesday, November 6, 2018 10:10 AM

  So my query looks like this below. When i put @month as 11 it works fine and gives 2019-01. but it does not work when i do @month=12 and show the out of range error. can someone help with same. I am trying to get output as-2019-02.

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(2018 as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)  
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(2018 as varchar)+'-'+case when 11 <10 then replicate('0',1)+cast(11 as varchar)
else cast(11+1 as varchar) end + '-01'),120)  --output-2019-01

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(2018 as varchar)+'-'+case when 12 <10 then replicate('0',1)+cast(12 as varchar) 
else cast(12+1 as varchar) end + '-01'),120)  --Error

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(2018 as varchar)+'-'+case when 12 <10 then replicate('0',1)+cast(12 as varchar)
  else cast(12+1 as varchar) end + '-01'),120) --Error
  12 + 1 = 13, there is no month 13.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 6, 2018 at 10:47 am

  #2012587

  Use the table in your other thread here, https://www.sqlservercentral.com/Forums/2008707/get-qtrmonth-and-year-from-start-date?Update=1#bm2008841, using the [fy_year] and [fy_month] columns to create the YYYY-MM, like this:
  CAST([fy_month] as varchar(4)) + '-' + right('0' + cast([fy_month] as varchar(2)),2)

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 6, 2018 at 11:13 am

  #2012588

  Jason A. Long - Tuesday, November 6, 2018 10:30 AM

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

  I am passing in the Year variable too and its set as 2018. Is there any way to pass in year variable in here to 2018 and it goes from there?

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@Year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 6, 2018 at 12:10 pm

  #2012601

  Papil - Tuesday, November 6, 2018 11:13 AM

  Jason A. Long - Tuesday, November 6, 2018 10:30 AM

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

  I am passing in the Year variable too and its set as 2018. Is there any way to pass in year variable in here to 2018 and it goes from there?

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@Year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  Passing into what?

  Seems to me you can use the table you created in that other thread to provide the values you need.  Heck, you can add another column with that information calculated already.

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 6, 2018 at 12:14 pm

  #2012602

  Lynn Pettis - Tuesday, November 6, 2018 12:10 PM

  Papil - Tuesday, November 6, 2018 11:13 AM

  Jason A. Long - Tuesday, November 6, 2018 10:30 AM

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

  I am passing in the Year variable too and its set as 2018. Is there any way to pass in year variable in here to 2018 and it goes from there?

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@Year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  Passing into what?

  This is how it is. Its a loop running 3 times to get data for 2018-11,2018-12 and 2019-01

  declare @Year INT
  ,@Month INT
  SET @Year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @Month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
  WHILE @i < 3
  BEGIN
    SET @i = @i + 1

  ----my query here

     SET @Month = @Month + 1;
      
  END

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 6, 2018 at 12:26 pm

  #2012605

  Papil - Tuesday, November 6, 2018 12:14 PM

  Lynn Pettis - Tuesday, November 6, 2018 12:10 PM

  Papil - Tuesday, November 6, 2018 11:13 AM

  Jason A. Long - Tuesday, November 6, 2018 10:30 AM

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

  I am passing in the Year variable too and its set as 2018. Is there any way to pass in year variable in here to 2018 and it goes from there?

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@Year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  Passing into what?

  This is how it is. Its a loop running 3 times to get data for 2018-11,2018-12 and 2019-01

  declare @Year INT
  ,@Month INT
  SET @Year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @Month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
  WHILE @i < 3
  BEGIN
    SET @i = @i + 1

  ----my query here

     SET @Month = @Month + 1;
      
  END

  Does this help?
  
declare @Year INT
,@Month INT
SET @Year= (SELECT DATEPART(YYYY, GetDate()));
SET @Month =(SELECT FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'));

  DECLARE @i int = 0
WHILE @i < 3
BEGIN
select cast(datepart(year,dateadd(month,@i,getdate())) as varchar(4)) + '-' + right('0' + cast(datepart(month,dateadd(month,@i,getdate())) as varchar(2)),2);
set @i = @i + 1;
end


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 6, 2018 at 12:27 pm

  #2012607

  Also, you really shouldn't be using a loop for this.  You could put this into the table from your other thread.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 6, 2018 at 12:32 pm

  #2012609

  Sort of like this:
  
create table [dbo].[mytable]
(
[StartDate] date not null primary key
, [CalendarMonth] as datepart(month,[StartDate]) persisted
, [CalendarYear] as datepart(year,[StartDate]) persisted
, [fy_year]  as datepart(year,dateadd(month,11,[StartDate])) persisted
, [fy_qtr]  as datepart(quarter,dateadd(month,11,[StartDate])) persisted
, [fy_month]  as datepart(month,dateadd(month,11,[StartDate])) persisted
, [Calendar_YearMonth] as cast(datepart(year,[StartDate]) as varchar(4)) + '-' + right('0' + cast(datepart(month,[StartDate]) as varchar(2)),2) persisted
, [fy_YearMonth] as cast(datepart(year,dateadd(month,11,[StartDate])) as varchar(4)) + '-' + right('0' + cast(datepart(month,dateadd(month,11,[StartDate])) as varchar(2)),2) persisted
);
go
INSERT INTO mytable(StartDate)
values
('2018-02-01'),
('2018-03-01'),
('2018-04-01'),
('2018-05-01'),
('2018-06-01'),
('2018-07-01'),
('2018-08-01'),
('2018-09-01'),
('2018-10-01'),
('2018-11-01'),
('2018-12-01'),
('2019-01-01');
go
select * from [dbo].[mytable];
go
drop table [dbo].[mytable]; -- clean up my SANDBOX DATABASE!!!!!
go

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  November 6, 2018 at 1:18 pm

  #2012613

  Papil - Tuesday, November 6, 2018 12:14 PM

  This is how it is. Its a loop running 3 times to get data for 2018-11,2018-12 and 2019-01

  declare @Year INT
  ,@Month INT
  SET @Year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @Month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
  WHILE @i < 3
  BEGIN
    SET @i = @i + 1

  ----my query here

     SET @Month = @Month + 1;
      
  END

  No need for a loop. Just use something like this...

  DECLARE
    @year INT = 2018,
    @month INT = 12;

  SELECT
    DT_Key = CONVERT(CHAR(7), DATEADD(MONTH, mr.month_range, DATEFROMPARTS(@year, @month, 1)), 21)
FROM
    ( VALUES (0),(1),(2) ) mr (month_range);

  Results...
  DT_Key
-------
2018-12
2019-01
2019-02

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 6, 2018 at 1:50 pm

  #2012619

  Lynn Pettis - Tuesday, November 6, 2018 12:26 PM

  Papil - Tuesday, November 6, 2018 12:14 PM

  Lynn Pettis - Tuesday, November 6, 2018 12:10 PM

  Papil - Tuesday, November 6, 2018 11:13 AM

  Jason A. Long - Tuesday, November 6, 2018 10:30 AM

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

  I am passing in the Year variable too and its set as 2018. Is there any way to pass in year variable in here to 2018 and it goes from there?

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@Year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  Passing into what?

  This is how it is. Its a loop running 3 times to get data for 2018-11,2018-12 and 2019-01

  declare @Year INT
  ,@Month INT
  SET @Year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @Month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
  WHILE @i < 3
  BEGIN
    SET @i = @i + 1

  ----my query here

     SET @Month = @Month + 1;
      
  END

  Does this help?
  
declare @Year INT
,@Month INT
SET @Year= (SELECT DATEPART(YYYY, GetDate()));
SET @Month =(SELECT FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'));

  DECLARE @i int = 0
WHILE @i < 3
BEGIN
select cast(datepart(year,dateadd(month,@i,getdate())) as varchar(4)) + '-' + right('0' + cast(datepart(month,dateadd(month,@i,getdate())) as varchar(2)),2);
set @i = @i + 1;
end


  I am trying to replace this code. I am subtracting the value of 2019-02 from 2019-01 ,2019-01 from 2018-12 etc and taking average.

  declare @year INT
  ,@month INT
  SET @year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
WHILE @i < 3
BEGIN
  SET @i = @i + 1
  insert into @Temp6(Period1,Volume1,Volume2)
    values (CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120),

       (Select Volume1 from @Temp6 where Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))+

  ((Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))-
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))+
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))-
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -2, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))
)/2
    ,(Select Volume2 from @Temp6 where period1=CONVERT(VARCHAR(7),
    DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)))

  so output be like-
  Period1  Volume1   Volume2
  2018-11  data          data
  2018-12   data         data
  2019-01  data        data

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 6, 2018 at 2:03 pm

  #2012620

  Papil - Tuesday, November 6, 2018 1:50 PM

  Lynn Pettis - Tuesday, November 6, 2018 12:26 PM

  Papil - Tuesday, November 6, 2018 12:14 PM

  Lynn Pettis - Tuesday, November 6, 2018 12:10 PM

  Papil - Tuesday, November 6, 2018 11:13 AM

  Jason A. Long - Tuesday, November 6, 2018 10:30 AM

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

  I am passing in the Year variable too and its set as 2018. Is there any way to pass in year variable in here to 2018 and it goes from there?

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@Year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  Passing into what?

  This is how it is. Its a loop running 3 times to get data for 2018-11,2018-12 and 2019-01

  declare @Year INT
  ,@Month INT
  SET @Year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @Month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
  WHILE @i < 3
  BEGIN
    SET @i = @i + 1

  ----my query here

     SET @Month = @Month + 1;
      
  END

  Does this help?
  
declare @Year INT
,@Month INT
SET @Year= (SELECT DATEPART(YYYY, GetDate()));
SET @Month =(SELECT FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'));

  DECLARE @i int = 0
WHILE @i < 3
BEGIN
select cast(datepart(year,dateadd(month,@i,getdate())) as varchar(4)) + '-' + right('0' + cast(datepart(month,dateadd(month,@i,getdate())) as varchar(2)),2);
set @i = @i + 1;
end


  I am trying to replace this code. I am subtracting the value of 2019-02 from 2019-01 ,2019-01 from 2018-12 etc and taking average.

  declare @year INT
  ,@month INT
  SET @year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
WHILE @i < 3
BEGIN
  SET @i = @i + 1
  insert into @Temp6(Period1,Volume1,Volume2)
    values (CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120),

       (Select Volume1 from @Temp6 where Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))+

  ((Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))-
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))+
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))-
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -2, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))
)/2
    ,(Select Volume2 from @Temp6 where period1=CONVERT(VARCHAR(7),
    DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)))

  so output be like-
  Period1  Volume1   Volume2
  2018-11  data          data
  2018-12   data         data
  2019-01  data        data

  Post what it is you are actually trying to accomplish, DDL (CREATE TABLE statement) for the table(s) involved, sample data (not production data) using TVC format (look it up in Microsoft Documentation), and the expected results based on the sample data.

  Personally, I am not going to sit here and pull teeth from a chicken trying to help you in a piecemeal fashion.  You want help, help us help you.

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 6, 2018 at 2:16 pm

  #2012622

  Lynn Pettis - Tuesday, November 6, 2018 2:03 PM

  Papil - Tuesday, November 6, 2018 1:50 PM

  Lynn Pettis - Tuesday, November 6, 2018 12:26 PM

  Papil - Tuesday, November 6, 2018 12:14 PM

  Lynn Pettis - Tuesday, November 6, 2018 12:10 PM

  Papil - Tuesday, November 6, 2018 11:13 AM

  Jason A. Long - Tuesday, November 6, 2018 10:30 AM

  Looks like you're trying to over complicate things...

  DECLARE @month INT = 12;

  SELECT CONVERT(CHAR(7), DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(GETDATE()), @month, 1)), 21);

  I am passing in the Year variable too and its set as 2018. Is there any way to pass in year variable in here to 2018 and it goes from there?

  select CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@Year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)else cast(@month+1 as varchar) end + '-01'),120)

  Passing into what?

  This is how it is. Its a loop running 3 times to get data for 2018-11,2018-12 and 2019-01

  declare @Year INT
  ,@Month INT
  SET @Year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @Month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
  WHILE @i < 3
  BEGIN
    SET @i = @i + 1

  ----my query here

     SET @Month = @Month + 1;
      
  END

  Does this help?
  
declare @Year INT
,@Month INT
SET @Year= (SELECT DATEPART(YYYY, GetDate()));
SET @Month =(SELECT FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'));

  DECLARE @i int = 0
WHILE @i < 3
BEGIN
select cast(datepart(year,dateadd(month,@i,getdate())) as varchar(4)) + '-' + right('0' + cast(datepart(month,dateadd(month,@i,getdate())) as varchar(2)),2);
set @i = @i + 1;
end


  I am trying to replace this code. I am subtracting the value of 2019-02 from 2019-01 ,2019-01 from 2018-12 etc and taking average.

  declare @year INT
  ,@month INT
  SET @year= (SELECT   DATEPART(YYYY, GetDate()))
  SET @month =(SELECT   FORMAT(DATEADD(Month, -1, GETDATE()), 'MM'))

  DECLARE @i int = 0
WHILE @i < 3
BEGIN
  SET @i = @i + 1
  insert into @Temp6(Period1,Volume1,Volume2)
    values (CONVERT(VARCHAR(7),DATEADD(MONTH, 1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120),

       (Select Volume1 from @Temp6 where Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))+

  ((Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))-
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))+
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -1, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))-
(Select Volume1 from @Temp6 where
Period1=CONVERT(VARCHAR(7),DATEADD(MONTH, -2, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120))
)/2
    ,(Select Volume2 from @Temp6 where period1=CONVERT(VARCHAR(7),
    DATEADD(MONTH, 0, CAST(@year as varchar)+'-'+case when @month <10 then replicate('0',1)+cast(@month as varchar)
else cast(@month+1 as varchar) end + '-01'),120)))

  so output be like-
  Period1  Volume1   Volume2
  2018-11  data          data
  2018-12   data         data
  2019-01  data        data

  Post what it is you are actually trying to accomplish, DDL (CREATE TABLE statement) for the table(s) involved, sample data (not production data) using TVC format (look it up in Microsoft Documentation), and the expected results based on the sample data.

  Personally, I am not going to sit here and pull teeth from a chicken trying to help you in a piecemeal fashion.  You want help, help us help you.

  ok i will do that. sorry for the confusion.