Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Selection using date held as a string Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:16 AM
Points: 188, Visits: 771
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?

Post #1437789
Posted Tuesday, April 2, 2013 6:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 28, 2014 10:50 AM
Points: 9, Visits: 26
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())

Post #1437855
Posted Tuesday, April 2, 2013 6:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:16 AM
Points: 188, Visits: 771
It still fails with the same error
Post #1437856
Posted Tuesday, April 2, 2013 6:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1437859
Posted Tuesday, April 2, 2013 6:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:16 AM
Points: 188, Visits: 771
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!
Post #1437869
Posted Tuesday, April 2, 2013 8:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:33 AM
Points: 2,616, Visits: 4,720
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/
Post #1437903
Posted Tuesday, April 2, 2013 9:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
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





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)
Post #1437953
Posted Tuesday, April 2, 2013 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 28, 2014 10:50 AM
Points: 9, Visits: 26
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())
Post #1437988
Posted Tuesday, April 2, 2013 10:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
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.



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)
Post #1437999
Posted Wednesday, April 3, 2013 1:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:16 AM
Points: 188, Visits: 771
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
Post #1438189
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse