SQL: Can't insert a date loop into a column

Question

I have a table like this:

id: PK bigint
RatePercent: decimal(4, 4)
DateRange: date

I am trying to populate the table as follows:

• RatePercentage with all of them 0.12
• Date starting from '01-01-2015' to '12-31-2099'

Unfortunately with my query it won't do that and it keeps saying that

Operand type clash: date is incompatible with int

I haven't assigned an int datatype asides from the id bigint. I'm a bit confused.

Here is my query so far:

DECLARE @Date Date
SET @Date = '01-01-2015'

WHILE @Date <= '12-31-2099'
BEGIN
    INSERT INTO [dbo].[IMF_Main_VATHistory] (VATRate, VATDate)
    VALUES (0.12, @Date + 1);
END

Answer 1

Try this:

DECLARE @Date Date
SET @Date = '01-01-2015'

WHILE @Date <= '12-31-2099'
BEGIN
    INSERT INTO [dbo].[IMF_Main_VATHistory] (VATRate, VATDate)
    VALUES (0.12, DATEADD(DAY, 1, @Date));

    SET @Date = DATEADD(DAY, 1, @Date);
END

You can't issue a direct addition to a DATE datatype, in SQL Server (for reference, I think you can in Oracle). You have to use functions in order to modify a DATE/DATETIME variable (or column).

Here is an example SQLFiddle.

Answer 2

The problem is in you "@Date + 1" I think - The SQL-Server likes to try and convert to INT :)

Use DATEADD that should work

DECLARE @Date Date
SET @Date = '01-01-2015'

WHILE @Date <= '12-31-2099'
BEGIN
    INSERT INTO [dbo].[IMF_Main_VATHistory] (VATRate, VATDate)
    VALUES (0.12, @Date);
    SET @Date = DATEADD(DAY, 1, @Date);
END