I have this cursor and the Where 'between' gives error. I wanted to select the years 2005 to 2008

Question

DECLARE @Year int
DECLARE db_cursor CURSOR FOR
Select distinct Year(OrderDate) as Year
From Sales.SalesOrderHeader
Order by Year(OrderDate)

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @Year
WHILE @@FETCH_STATUS = 0   
BEGIN

--INSERT INTO xxxxx ("Year", "Month", "Total", "RunningTotal")
SELECT a.Year, a.Month, Sum(a.TotalDue) as Total, Sum(a.RunningTotal) as RunningTotal
From (
    select Year = Year(convert(int,OrderDate,111)),
        case Month(convert(int,OrderDate,111))
            when 1 then 'Janeiro'
            when 2 then 'Fevereiro'
            when 3 then 'Março'
            when 4 then 'Abril'
            when 5 then 'Maio'
            when 6 then 'Junho'
            when 7 then 'Julho'
            when 8 then 'Agosto'
            when 9 then 'Setembro'
            when 10 then 'Outubro'
            when 11 then 'Novembro'
            when 12 then 'Dezembro'
        else 'unknown'
        end as "Month1",
        Month = Month(convert(int,OrderDate,111)),
        TotalDue = convert(money,TotalDue,1),
        RunningTotal = convert(money,
            (SELECT sum(convert(money, TotalDue,1))
             FROM   Sales.SalesOrderHeader as Header
             WHERE  SalesOrderID <= soh.SalesOrderID
                AND year(OrderDate) '2005' between '2008'
                ),
            1)
    FROM Sales.SalesOrderHeader soh
    WHERE year(OrderDate) '2005' between '2008'
) a
group by a.Year, a.Month
order by 1, 2;

FETCH NEXT FROM db_cursor INTO @Year
END

CLOSE db_cursor
DEALLOCATE db_cursor

--End Cursor

Answer 1

The syntax is wrong...

has to be

WHERE year(OrderDate) between '2005' and '2008'

Answer 2

You have two syntax error that I could see right away, the first is the use of BETWEEN

AND year(OrderDate) '2005' between '2008'

The correct syntax is

WHERE col BETWEEN value1 AND value2

And your other mistake and compare the return of your YEAR with a varchar, the year returns an int so compare with an int.

The Correct would be

WHERE year(OrderDate) between 2005 and 2008

Some things in your query are unnecessary like Month(convert(int,getdate(),111)) there is no need here to use the Convert when you can use the Month to return the month see the select below return the same result.

select Month(convert(int,getdate(),111)), Month(getdate())

Same goes for Year.

I don’t know what the idea of using CURSOR ?? you made it and didn’t use it for anything...

DECLARE @Year int
DECLARE db_cursor CURSOR FOR
Select distinct Year(OrderDate) as Year
From Sales.SalesOrderHeader
Order by Year(OrderDate)

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @Year
WHILE @@FETCH_STATUS = 0 

If your goal is to make an Insert with the return data based on a range of YEARS (2005 - 2008) use a lool for and avoid making a select without need.

DECLARE @ano INT = 2005;

WHILE @ano < 2009
BEGIN
   {-- todo}
   SET @ano = @ano + 1;
END;

Use the variable you incremented in your loop to select

AND year(OrderDate) = @ano;

in place of his AND year(OrderDate) '2005' between '2008'

and finally if you are only going to do the Insert in this interval even then use only the between and remove from inside your CURSOR it will only duplicate your data the way this.

Answer 3

I believe your error is really syntax; use the between as below:

WHERE year(OrderDate) between '2005' and '2008'

NOTE: Notice that the two times you use the between error happens, then corrects at two points.