|
|
Posted by Erland Sommarskog on 06/21/06 21:33
Filips Benoit (benoit.filips@pandora.be) writes:
> -- dates in CPROP_VALUE ( nvarchar) as '1/1/2000' or'01/01/200' or
> '1/06/2000' tested in access with function IsDate()
>
> As
>
> SELECT CPROP_PRP_ID,
> CPROP_VALUE
> FROM dbo.COMPANY_PROPERTY
> where CPROP_PRP_ID = 370 and
> convert(datetime, CPROP_VALUE, 103 ) = getdate()
As I understood it, CPROP_VALUE can include all sorts of values, including
stringified floats. Not all these values will convert to datetime,
and will give you an error of some sort.
Yes, I can guess that if CPROP_PRP_ID = 370, then all values are good
date strings, but there is no operator shortcutting in T-SQL. The
only way to avoid undesired conversion attempts is to use CASE:
WHERE CASE CPROP_PRP_ID
WHEN 370 THEN convert(datetime, CPROP_VALUE, 103 )
END = getdate()
AND CPROP_PRP_ID = 370
The last condition is logically redudant, but if you have an index on
CPROP_PRP_ID, it's a good thing to add it.
Note the above query is not extremely meaningful. getdate() returns
the current date and time with a precisions on 3.33 ms. The odds that
the SELECT will return any rows appear slim.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|