Chris Quinn-821458 (4/4/2013)
I think I have discovered something that is possibly significant!As stated previously, the data is a set of key/value pairs, with a datatype (D = Date, N= Number, S = String, B = Bit)
If the table contains only data of type D, my query works, but if there is data of any other type in the table, I get the conversion error message, even though my WHERE clause explicitly excludes everything except those where the datatype = 'D'. This suggests that it is evaluating the date comparison criteria before the datatype = 'D' clause, and the conversion is failing on non-date datatypes.
I therefore put the WHERE datatype = 'D' selection into a derived view of the table, assuming this would be evaluated before the date comparisons later in the query, but this is appears not to be the case unless I put in a kludge to force the matter
This fails:
SELECT DERIVED.*
FROM (select parameter_value PV , ,convert(datetime2,PV.parameter_value) as key_date WHERE PV.datatype = 'D') DERIVED
WHERE DERIVED.key_date BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())
But this works!
SELECT DERIVED.*
FROM (select TOP 9999999999 parameter_value PV , ,convert(datetime2,PV.parameter_value) as key_date WHERE PV.datatype = 'D') DERIVED
WHERE DERIVED.key_date BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())
I don't have the same data as you, but I don't have any problems with the following:
create table dbo.parameter_value (
datatype char(1),
key_value varchar(64)
);
insert into dbo.parameter_value(datatype, key_value)
values
('D','2013-10-29 22:59:00Z'),
('B','0'),
('N','101'),
('N','102'),
('B','1'),
('D','2013-04-25 23:59:00Z'),
('N','105'),
('N','106'),
('D','2013-03-06 22:59:00Z'),
('D','2013-03-06 22:59:00Z'),
('N','103'),
('B','0'),
('B','1'),
('N','104'),
('N','107'),
('N','110'),
('D','2013-03-06 22:59:00Z'),
('B','0'),
('B','0'),
('D','2013-03-29 22:59:00Z'),
('D','2013-03-06 22:59:00Z'),
('B','1'),
('B','0'),
('B','1'),
('D','2013-04-27 23:59:00Z'),
('B','1'),
('D','2013-03-06 22:59:00Z'),
('D','2013-04-08 23:59:00Z'),
('N','108'),
('N','109');
go
SELECT
key_value
,convert(datetime2,PV.key_value) as DT2
,convert(datetime,CONVERT(DATETIME2, PV.key_value)) AS DT
FROM
parameter_value PV
WHERE
PV.datatype = 'D';
go
SELECT
key_value
,convert(datetime2,PV.key_value) as DT2
,convert(datetime,CONVERT(DATETIME2, PV.key_value)) AS DT
FROM
parameter_value PV
WHERE
PV.datatype = 'D'
and convert(datetime,CONVERT(DATETIME2, PV.key_value)) BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())
go
drop table dbo.parameter_value;
go