Bug in 2005?

This code snippet works fine in 2000 but not in 2005. Is this a bug? I
get a conversion error on the 2005 server. Any ideas?
Create table Sample
(DESCR Char(30),
Ref Char(50),
Name Char(30))
Insert into sample
values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
Insert into sample
values ('Something else','Not date related','Jack')
DECLARE @.Ref_DT datetime
SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
Select Name From Sample
Where SubString(DESCR, 1, 20) = 'Valid Row'
and Convert(datetime, REF, 121) = @.Ref_DTshub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
> Create table Sample
> (DESCR Char(30),
> Ref Char(50),
> Name Char(30))
> Insert into sample
> values ('Valid Row','03/25/2006 02:30:15 PM','Joe')
> Insert into sample
> values ('Something else','Not date related','Jack')
> DECLARE @.Ref_DT datetime
> SELECT @.Ref_DT = '03/25/2006 02:30:15 PM'
> Select Name From Sample
> Where SubString(DESCR, 1, 20) = 'Valid Row'
> and Convert(datetime, REF, 121) = @.Ref_DT
>
The "bug" is that you're trying to convert a non-date value to a
DATETIME. How is this SQL's fault?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||shub wrote:
> This code snippet works fine in 2000 but not in 2005. Is this a bug? I
> get a conversion error on the 2005 server. Any ideas?
>
Evaluation order is not guaranteed in any query so this can't be called
a bug even though it is inconvenient.
In the example given it would anyway be much better to make @.Ref_DT a
CHAR(50) instead of DATETIME. That way you can avoid the conversion for
each row.
If you must use CONVERT then try making a CASE expression of it (watch
out for line wrapping in this example):
...
WHERE SUBSTRING(DESCR, 1, 20) = 'Valid Row'
AND CASE WHEN REF
LIKE '[012][0-9]/[0123][0-9]/[12][0-9][0-9][
0-9]
[012][0-9]:[0-5][0-9]:[0-5][0-9] [AP]M'
THEN CONVERT(DATETIME, REF, 121)
END = @.Ref_DT;
The above isn't foolproof but it does at least force the right
evaluation order (usually). In your case you can also use the ISDATE
function in place of my LIKE expression. ISDATE has the disadvantage
that it depends on implicit conversion so it isn't suitable for all
date formats.
The most important lesson is, don't store dates as strings.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--