Posted by Erland Sommarskog on 12/08/05 22:38

TGEAR (ted_gear@hotmail.com) writes:
> each table has a auditime field which keeps the last data who modified.
> I set this field as a datetime datatype and default value is getdate(),
> so the value is recorded like this 10/12/2004 7:28:02 AM.
> When I query to get that value, nothing showed up.
> I made a query as below
> select auditime from testtable where auditime = '10/12/2004 7:28:02 AM'
> but nothing was fetched. what did i do wrong?

First of all, when you work with date literals in SQL Server always use
the format YYYYMMDD, as this format can only be interpreted in one way.
depending on settings 10/12/2004 can be interpreted as Dec 10th or Oct 12th.

Then as Jens and David pointed out, there are milliseconds as well, which
you need to cater for:

SELECT .. WHERE audittime >= '20041210 07:28:02' and
audittime < '20041210 07:28:03'




--
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

[Back to original message]