473,323 Members | 1,537 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Is it a SQL Server 2000 bug

PFI
Hi, I'm currently using SQL Server 2000 SP3.
I'm facing some problems which make me think of a SQL Server BUG.

When executing a query, it seems that SQL Server tries to convert data
even if not in the resultset -> This leads to SQL Server error.

Here is a very easy sample to reproduce it :

SET NOCOUNT ON
create table tempdb..test ( coldate varchar(30) )

insert into tempdb..test values ( '1900/01/01 00:00:00' )
insert into tempdb..test values ( '2005/01/01 00:00:00' )
insert into tempdb..test values ( 'Invalid date' )

select * from tempdb..test
where isdate( coldate ) = 1
and convert( datetime, coldate ) > GETDATE()

SQL Server output is the following :

Server: Msg 241, Level 16, State 1, Line 10
Syntax error converting datetime from character string.

SQL Server considers as an error the 'Invalid date' even if a filter
ISDATE = 1 is applied....
I guess that I am not able to determine the order in which filters are
applied as it is SQL Server optimizer job... However, this should lead
to an error only if conversion fails on a line of the resultset after
application of all other filters....

What am I doing wrong ?

Thanks
Patrick

Jul 23 '05 #1
6 2362
Not a bug. The execution order is determined by the query optimizer.
There is no reason to suppose that the ISDATE expression will always
execute first.

What are you doing wrong? Firstly, you are writing queries against
dates stored as strings. If at all possible you should convert the
dates to use a proper DATETIME column. If you really cannot do that
then you should be able to rewrite your query using a derived table.

The second thing wrong here is that you are relying on an implicit
conversion from a non-standard date format. Those conversions are
sensitive to local server and connection settings so avoid them. Try
the following:

SELECT coldate
FROM
(SELECT REPLACE(REPLACE(coldate,'/','-'),' ','T') AS coldate
FROM tempdb..test
WHERE ISDATE(REPLACE(REPLACE(coldate,'/','-'),' ','T'))=1) AS T
WHERE CAST(coldate AS DATETIME) > CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
Thanks for the answer.
However, I thought that the optimizer was designed only to optimize the
query but resultset was independant of the order of execution of
filters.
Here, it appears that depending on the order of application of filters,
result is not the same....
I guess that this is a special case due to the abuse of conversion
use...

Thanks
Patrick
*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #3
Hi, Patrick

For details about this problem and possible solutions, see this article
by Itzik Ben-Gan, SQL Server MVP:
http://www.windowsitpro.com/Windows/...48/pg/1/1.html

Razvan

Jul 23 '05 #4
No, an SQL Statement is a specification of the result, and
theoretically, it is created "all at once". It is not good enough that
you will get correct results if the query is executed "left to right,
top to bottom", because that is not how SQL works.

A solution for your problem could be to place the two related predicates
in a case expression. A case expression always has to be evaluated from
left to right.

select * from tempdb..test
where CASE WHEN isdate( coldate ) = 0 THEN 0
WHEN convert( datetime, coldate ) > GETDATE() THEN 1
ELSE 0 END = 1

Hope this helps,
Gert-Jan
Patrick Fiche wrote:

Thanks for the answer.
However, I thought that the optimizer was designed only to optimize the
query but resultset was independant of the order of execution of
filters.
Here, it appears that depending on the order of application of filters,
result is not the same....
I guess that this is a special case due to the abuse of conversion
use...

Thanks
Patrick

*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '05 #5
Here is another way:

select * from tempdb..test where case when isdate( coldate ) = 1
then convert( datetime, coldate ) else getdate() end > getdate()

Razvan

Jul 23 '05 #6
PFI
Thanks all for your help.

Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Thue Tuxen Sørensen | last post by:
Hi everybody ! I´m maintaining a large intranet (approx 10000 concurrent users) running on one IIS box and one DB box with sqlserver 2000. Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2...
2
by: Jay Chan | last post by:
We have just installed a SQL Server 2000 (SP 3A) onto a computer that has Windows-2003 Server on it. Now, we cannot get access to that database server from other computers. Seem like this may be an...
0
by: Chris Halcrow | last post by:
Hi I've spent ALL DAY trying to re-install SQL Server 2000 on Windows XP. I continually get the error 'cannot configure server' just at the end of the installation. I've tried the following: ...
2
by: Dave | last post by:
I have developed a VB.net application that transfers a SOAP wrapped message to another server securely(using SSL and sever/client certificates). When I run this app from a Windows 2000...
4
by: Glenn M | last post by:
I have a shared XML file on a server . i also have one xslt file that performs a simple transform on in to view the data. now i want to have another page that lets users modify the shared xml...
22
by: EP | last post by:
When running my asp.net hosting service (asp.net without IIS), on server 2003 with IIS not installed, I get the following when trying to process a request. "System.DllNotFoundException: Unable to...
17
by: Danieltbt05 | last post by:
just installed SQL server 2000 and using my client , i can't locate the server. I used SQL query analyzer to search but no servers were found. Error message is as below Server : Msg17,level...
14
by: Developer | last post by:
Hello All, i have recently installed VS2005 and was trying to install SQL sever 2000. I have Win XP' SP2. But when I tried installing, it only installed client tools and not the database. Can...
0
by: tamayi | last post by:
I have a problem (like most others posting issues on this forum :) ) I have a remote server running Windows XP SP2, with both SQL Server 2005 Express with Advanced Features and SQL 2000...
10
by: Scott M. | last post by:
I've seen many posts and ready articles discussing how changing the membership & roles "provider" in VS .NET is easy, but have yet to see instructions on how to do it. If I already have SQL...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.