April 2, 2013 at 4:11 am
In our database we have a parameters table that holds a parameter type (e.g. D for date, N for number, I for integer etc) and then a varchar column which holds the parameter value.
In the case of dates, the data is held in the format 'yyyy-mm-dd hh:mm:ssZ' which will convert quite hapily into a Datetime2 datatype for display
Example Data values (key_value):
2013-10-29 22:59:00Z
2013-04-25 23:59:00Z
2013-03-06 22:59:00Z
2013-03-06 22:59:00Z
2013-03-06 22:59:00Z
2013-03-29 22:59:00Z
2013-03-06 22:59:00Z
2013-04-27 23:59:00Z
2013-03-06 22:59:00Z
2013-04-03 23:59:00Z
SELECT TOP 10 key_value
,convert(datetime2,BPK.key_value) as DT2
,convert(datetime,CONVERT(DATETIME2, PV.key_value)) AS DT
FROM parameter_value PV
WHERE PV.datatype = 'D'
Displaying the data is not a problem, but when I try to use the data in selection criteria, it fails with a 'Conversion failed when converting date and/or time from character string.'
e.g.
SELECT TOP 10 key_value
,convert(datetime2,BPK.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())
It doesn't seem to matter how I convert the data - to Dattime, Datetime2, Varchar etc - I always get the conversion error when the data is used in a WHERE clause.
Any insights?
April 2, 2013 at 6:11 am
use this.
SELECT TOP 10 key_value
,convert(datetime2,BPK.key_value) as DT2
,convert(datetime,CONVERT(DATETIME2, PV.key_value)) AS DT
FROM parameter_value PV
WHERE PV.datatype = 'D'
and convert(datetime2,replace(PV.key_value,right(PV.key_value,4),'')) BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())
April 2, 2013 at 6:17 am
It still fails with the same error
April 2, 2013 at 6:25 am
You will have to use DERIVED TABLE to do this
SELECTTOP 10 *
FROM(
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'
) AS PV
WHEREconvert(datetime,CONVERT(DATETIME2, PV.key_value)) BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())
This issue occurs because the query optimizer is evaluating the WHERE CLAUSE involving key_value first and then the one for datatype
Hence, its better to force it this way using a DERIVED TABLE.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 2, 2013 at 6:55 am
I have been trying the derived table route, but have hit a very strange problem - I have 50 records in my table with a Type of D and a date value.
If I do this:
select * from (
select key_value, CONVERT(DATETIME2, key_value) AS key_date
from parameter_values WHERE data_type = 'D') PV
where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())
It still fails.
If I do this (remember, there are 50 records meeting the data_type criterion:
select * from (
select TOP 100 key_value, CONVERT(DATETIME2, key_value) AS key_date
from parameter_values WHERE data_type = 'D') PV
where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())
it works!
Changing to this:
select * from (
select TOP 100 PERCENT key_value, CONVERT(DATETIME2, key_value) AS key_date
from parameter_values WHERE data_type = 'D') PV
where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())
fails, but the one below works!
select * from (
select TOP 99 PERCENT key_value, CONVERT(DATETIME2, key_value) AS key_date
from parameter_values WHERE data_type = 'D') PV
where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())
I am tearing my hair out!
April 2, 2013 at 8:01 am
It depends on the way SQL Server processes your query
We want the SQL Server to process the queries in the following orders
1. Process the WHERE CLAUSE based on datatype and then
2. Process the WHERE CLAUSE based on key_value( now dates )
But, SQL Server might do some calculation which will make it process the query in the reverse order
This will make SQL Server process the WHERE CLAUSE based on key_value( now dates ) first
While doing this, it will try to convert all key_values( even non-date values ) to datetime which results in an error
You can check the execution plan to see what is the case.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 2, 2013 at 9:06 am
The following runs without error on my system. I had to correct some errors in the code provided.
You will want to run the following in a sandbox database first as I do create and drop the parameter_value table.
--Example Data values (key_value):
create table dbo.parameter_value (
pvid int identity(1,1),
datatype char(1),
key_value varchar(64)
);
insert into dbo.parameter_value(datatype, key_value)
values
('D','2013-10-29 22:59:00Z'),
('D','2013-04-25 23:59:00Z'),
('D','2013-03-06 22:59:00Z'),
('D','2013-03-06 22:59:00Z'),
('D','2013-03-06 22:59:00Z'),
('D','2013-03-29 22:59:00Z'),
('D','2013-03-06 22:59:00Z'),
('D','2013-04-27 23:59:00Z'),
('D','2013-03-06 22:59:00Z'),
('D','2013-04-03 23:59:00Z');
go
SELECT TOP 10 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 TOP 10 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
April 2, 2013 at 10:03 am
issue is with varchar value 'z' in data ('2013-10-29 22:59:00Z) and you are converting it to datetime or datetime2.
you can convert it after removing that value from data.
use this.
SELECT TOP 10 key_value
,convert(datetime2,PV.key_value) as DT2
,convert(datetime,CONVERT(DATETIME2, left(PV.key_value,19))) AS DT
FROM parameter_value PV
WHERE PV.datatype = 'D'
and CONVERT(DATETIME,LEFT(PV.key_value,19)) BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())
April 2, 2013 at 10:28 am
umarrizwan (4/2/2013)
issue is with varchar value 'z' in data ('2013-10-29 22:59:00Z) and you are converting it to datetime or datetime2.you can convert it after removing that value from data.
use this.
SELECT TOP 10 key_value
,convert(datetime2,PV.key_value) as DT2
,convert(datetime,CONVERT(DATETIME2, left(PV.key_value,19))) AS DT
FROM parameter_value PV
WHERE PV.datatype = 'D'
and CONVERT(DATETIME,LEFT(PV.key_value,19)) BETWEEN GETDATE() and DATEADD(d, 10, GETDATE())
The code I posted above works just fine in SQL Server 2008, even with the Z in the data.
April 3, 2013 at 1:25 am
umarrizwan (4/2/2013)
issue is with varchar value 'z' in data ('2013-10-29 22:59:00Z) and you are converting it to datetime or datetime2.
Datetime2 handles the 'z', Datetime does not
April 4, 2013 at 3:29 am
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())
April 4, 2013 at 4:44 am
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply