I have a table that has one row per month and amounts are stored in different columns (DAY1, DAY2... DAY31). I have created a view that uses unpivot to split this into one row per day, so that I can do calculations for given date range.
When I try to use the view by selecting only certain date range, it ends up in error as long when I have DAY29..DAY31 in the table. If the table contains days only up to 28, then it works fine.
Unfortunately changing the table structure isn't really an option and I tried the same thing with inline function, but it ends up in the same error
Msg 242, Level 16, State 3, Line 52 The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
This is my table:
CREATE TABLE CONSUMPTION (
ID int NOT NULL,
YEAR int NOT NULL,
MONTH int NOT NULL,
DAY1 int NULL,
DAY2 int NULL,
DAY3 int NULL,
DAY31 int NULL,
CONSTRAINT TEST_PK PRIMARY KEY CLUSTERED (ID, YEAR, MONTH)
)
insert into CONSUMPTION values (1,2015,1,10,20,30,310)
insert into CONSUMPTION values (1,2015,2,10,20,30,NULL)
This is my view:
create view CONSUMPTION_CALENDAR as
select
ID,
YEAR,
MONTH,
convert(datetime, substring(COLNAME, 4,2) + '.' + convert(varchar, [MONTH]) + '.' + convert(varchar, [YEAR]), 104) as CONSDATE,
CONSKG
from
(
select * from CONSUMPTION
) S
unpivot (CONSKG for COLNAME in (DAY1,DAY2,DAY3,DAY31)) as UP
go
If I run it just like this, it works fine:
select * from CONSUMPTION_CALENDAR
But if I add criteria, it returns the data, but also fails:
select * from CONSUMPTION_CALENDAR where CONSDATE >= '20150101'
Is there any workaround that I would be able to select just certain date range?
Edit: Data in the view:
ID YEAR MONTH CONSDATE CONSKG
1 2015 1 2015-01-01 10
1 2015 1 2015-01-02 20
1 2015 1 2015-01-03 30
1 2015 1 2015-01-31 310
1 2015 2 2015-02-01 10
1 2015 2 2015-02-02 20
1 2015 2 2015-02-03 30
Example in SQL Fiddle.
Option 1
Create a calendar table that has date in suitable format, e.g. D.M.YYYY that can be joined with the unpivot. This way there is no conversion from unpivot string to dates, so it cannot fail.
create view CONSUMPTION_CALENDAR as
select
P.ID,
C.DAY,
P.MONTH,
P.YEAR,
C.CALENDARDATE as CONSDATE,
P.CONSKG
from (
select
ID,
YEAR,
MONTH,
ltrim(substring(COLNAME, 4,2)) + '.' + convert(varchar(2), [MONTH]) + '.' + convert(varchar(4), [YEAR]) as STOCKDATESTR,
CONSKG
from
(
select * from CONSUMPTION
) S
unpivot
(CONSKG for COLNAME in (DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,DAY7,DAY8,DAY9,DAY10,DAY11,DAY12,DAY13,DAY14,DAY15,DAY16,DAY17,DAY18,DAY19,DAY20,DAY21,DAY22,DAY23,DAY24,DAY25,DAY26,DAY27,DAY28,DAY29,DAY30,DAY31)) as UP
) P
join CALENDAR C on C.DATESTR = P.STOCKDATESTR
The CALENDAR table has dates in format D.M.YYYY without leading zeros in DATESTR and CALENDARDATE is Date.
Option 2
The fetch seems to work ok also with view like this, when NULLS are changed into 1.1.1900:
create view CONSUMPTION_CALENDAR as
select
ID,
YEAR,
MONTH,
convert(datetime,
case when CONSKG is NULL then '1.1.1900' else
substring(COLNAME, 4,2) + '.' + convert(varchar, [MONTH]) + '.' + convert(varchar, [YEAR]) end
, 104) as CONSDATE,
CONSKG
from
(
select * from CONSUMPTION
) S
unpivot (CONSKG for COLNAME in (DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,DAY7,DAY8,DAY9,DAY10,DAY11,DAY12,DAY13,DAY14,DAY15,DAY16,DAY17,DAY18,DAY19,DAY20,DAY21,DAY22,DAY23,DAY24,DAY25,DAY26,DAY27,DAY28,DAY29,DAY30,DAY31)) as UP;
Assuming there's no bad data in the table, this shouldn't fail.
Option 3
I was able to find out one way to prevent the problem by using top. I assume SQL Server is not able to move the where predicate from outside the top into it because in theory it could change the results, even if there is no order by:
select * from (
select top 1000000000 * from CONSUMPTION_CALENDAR
) X
where CONSDATE >= convert(datetime, '20150101')
This seems to work ok, but can't be sure if this starts to fail in certain cases.