SQL: unable to insert date loops into column
I have a table like this:
id: PK bigint
RatePercent: decimal(4, 4)
DateRange: date
I am trying to populate a table like this:
- RatePercentage with all of them 0.12
- Start date from '01 -01-2015 'to '12 -31-2099'
Unfortunately my request won't do it and it keeps saying that
Operand type collision: date is incompatible with int
I have not assigned int idatype asides from id bigint. I am a bit confused.
Here is my request:
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
source to share
I do not recommend using any loop based RBAR solution or . You can do this with a set based approach using Table Tally .
DECLARE @startDate DATE
DECLARE @endDate DATE
SET @startDate = '20150101'
SET @endDate = '20991231';
WITH E1(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS(SELECT 1 FROM E1 a, E1 b),
E4(N) AS(SELECT 1 FROM E2 a, E2 b),
E8(N) AS(SELECT 1 FROM E4 a, E4 b),
Tally(n) AS(
SELECT TOP(DATEDIFF(DAY, @startDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM E8
)
INSERT INTO IMF_Main_VATHistory(RatePercent, DateRange)
SELECT
0.02, DATEADD(DAY, N-1, @startDate)
FROM Tally
It is faster compared to using loops CURSOR
or WHILE
.
source to share
Try the following:
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 cannot directly add the datatype DATE
to SQL Server (for reference, I think you can in Oracle). You have to use functions to change the variable DATE
/ DATETIME
(or column).
Here's an example SQLFiddle .
source to share
The problem is you "@Date + 1" I think - SQL server loves to try and convert to INT :)
Use DATEADD , which 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
source to share