SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selection using date held as a string


Selection using date held as a string

Author
Message
Chris Quinn-821458
Chris Quinn-821458
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 843
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?
umarrizwan
umarrizwan
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 49
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())
Chris Quinn-821458
Chris Quinn-821458
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 843
It still fails with the same error
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3693 Visits: 5180
You will have to use DERIVED TABLE to do this

SELECT   TOP 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
WHERE convert(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.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Chris Quinn-821458
Chris Quinn-821458
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 843
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!
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3693 Visits: 5180
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.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39273 Visits: 38529
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
umarrizwan
umarrizwan
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 49
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())
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39273 Visits: 38529
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Chris Quinn-821458
Chris Quinn-821458
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 843
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search