<p>Since the first of this year, 2014, a portion of an old report I run (have run for years) has quit working and begun throwing this error: "nanos > 999999999 or < 0".</p><p>This part of the report makes a list of 30 dates (the last 30 days) and then collects power data from a database using that table of dates, storing the results in a new temporary table.</p><p>At first I thought I wasn't handling 1/1/2014 properly. But further investigation revealed that to make the query work, all I had to do was shorten the request from 30 days to 28 days, even if those 28 days did cross 1/1/2014.</p><p>This is the SQL:</p><p> </p><p>IF OBJECT_ID (N'tempdb.dbo.#dates', N'U') IS NOT NULL DROP TABLE #dates
IF OBJECT_ID (N'tempdb.dbo.#powerdata', N'U') IS NOT NULL DROP TABLE #powerdata</p><p>CREATE TABLE #dates(Dates DATETIME PRIMARY KEY)
CREATE TABLE #powerdata(Dates DATETIME, PD2_kWHNET INT, PD10_kWHNET INT, PD12_kWHNET INT, PD19_MWhr INT)</p><p>DECLARE
@Dates1 DATETIME,
@Dates2 DATETIME,
@Dates3 DATETIME,
@Dates4 varchar(23),
@Dates1a varchar(23),
@Dates2a varchar(23),
@count1 INT,
@count2 INT,
@date1 nvarchar(32),
@SQLString varchar(1000),
@PrevDate DATETIME,
@NextDate DATETIME,
@SMinus DATETIME,
@SPlus DATETIME,
@PrevDateStr varchar(23),
@NextDateStr varchar(23),
@SMinusStr varchar(23),
@SPlusStr varchar(23)</p><p>SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF</p><p>--GET LAST 30 DAYS AVERAGE--</p><p>--BUILD DATE LIST--</p><p>SET
@Dates3 = dateadd(day,-29,getdate())</p><p>WHILE
@Dates3 <= CONVERT(char(10),dateadd(day,1,getdate()),101)
BEGIN
INSERT INTO #dates(Dates)
VALUES(
@Dates3)
SET
@Dates3 = dateadd(day, 1,
@Dates3)
END</p><p>--***LOAD DATA FOR ALL DAYS***--</p><p>SELECT
@count1 = count(Dates) FROM #dates</p><p>SET
@count2 = 1
WHILE
@count1 >=
@count2 and
@count2 <=
@count1
BEGIN
SELECT TOP 1
@date1 = Dates FROM #dates;
DELETE FROM #dates WHERE Dates = (SELECT top 1 * FROM #dates);
SET
@SQLString = 'SET QUOTED_IDENTIFIER OFF
SELECT * FROM OpenQuery(INSQL,"SELECT DateTime,
[PD2-kWHNET] = [PD2-kWHNET-3]*1000000+[PD2-kWHNET-2]*1000+[PD2-kWHNET-1],
[PD10-kWHNET] = [PD10-kWHNET-3]*1000000+[PD10-kWHNET-2]*1000+[PD10-kWHNET-1],
[PD12-kWHNET] = [PD12-kWHNET-3]*1000000+[PD12-kWHNET-2]*1000+[PD12-kWHNET-1],
[PD19-MWhr]*1000
FROM dbo.AnalogWideHistory
WHERE DateTime = '''
+
@date1 +
'''
AND wwRetrievalMode = ''Delta''
AND wwVersion = ''LATEST''
" )';
INSERT INTO #powerdata EXEC(
@SQLString);
SET
@count2 =
@count2 + 1
END</p><p>SELECT * FROM #powerdata</p><p> </p><p>Proper results would be rows in the form of :</p><p>Jan 30, 2014 12:00 PM, 59509859, 41411464, 8884974, 2235000 </p><p> </p><p>You will notice the "SET
@Dates3 " line has a -29 in it and the following line has a dateadd of 1. That is the 30 days. If I change -29 to -27, it works. Anything greater than -27 and it doesn't work.</p><p> </p><p>I am using BIRT 4.2.0 but have tried this in 4.3.1 as well with the same result.</p><p> </p><p>The only references I could find on the web related to datetime problem so I tried various changes to how I put in the dates (nvarchars, varchars, datetime, etc.) but nothing changed the error.</p><p>As I said, this worked on 12/31/2013 and failed on 1/1/2014 without any changes to the report. Please let me know if you have any insight into how this error could be related to my 30 days request or what this error means.</p>
