datetime

[TGEAR]

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?

[Jens]

Due the the unprecision of the datetime column, which is only in a 3 ms
precision range (dependend on the way SQl Server evaluates the time
based on ticks) a row could not be fetch which
is apparently equals to your given expression, so converting it to the
same format (with chopping the ms) would evaluate the right rows:

CONVERT(VARCHAR(50),ColumnName,120) = CONVERT(VARCHAR(50),'10/12/2004
7:28:02 AM' ,120)

HTH, Jens Suessmeyer.

[TGEAR]

I tried as below, but got an error msg "syntax error converting
datetime from character string"
where did i do wrong again?

SELECT auditime
FROM testtable
WHERE (auditime = CONVERT(VARCHAR(50), '10/12/2004
7:28:02 AM', 120))

[Dave Owen]

Just in case: In Books on Line for SQL 2000, look for the article 'CAST and
CONVERT' to decode the mysterious 120 value Jens suggested.

"Jens" <Je**@sqlserver2005.de> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Due the the unprecision of the datetime column, which is only in a 3 ms
precision range (dependend on the way SQl Server evaluates the time
based on ticks) a row could not be fetch which
is apparently equals to your given expression, so converting it to the
same format (with chopping the ms) would evaluate the right rows:

CONVERT(VARCHAR(50),ColumnName,120) = CONVERT(VARCHAR(50),'10/12/2004
7:28:02 AM' ,120)

HTH, Jens Suessmeyer.

[Dave Owen]

Jens suggested changing the value stored in your database because of
inherent imprecisions. Since you didn't do that try bracketing your query:
where auditime = <sometime before the event> and auditime = <sometime after
the event>

"TGEAR" <te******@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I tried as below, but got an error msg "syntax error converting
datetime from character string"
where did i do wrong again?

SELECT auditime
FROM testtable
WHERE (auditime = CONVERT(VARCHAR(50), '10/12/2004
7:28:02 AM', 120))

[Erland Sommarskog]

TGEAR (te******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[manstein]

Hello TGEAR

Your column is defined as datetime datatype and you have a default
insert of the getdate() function with is also of datatype datetime
there for your value is not stored in the format '10/12/2004 7:28:02
AM' but instead is similar to: '2004-10-12 07:28:02.000'. I say
similar because it depends on the precision of the milliseconds. What
you need to do is format your where clause value to be compatable with
datetime values:

SELECT auditime
FROM testtable
WHERE convert(varchar(50),auditime,120) = '2004-10-12 07:28:02'

your good to go.

best regards

[Erland Sommarskog]

manstein (jk**********@gmail.com) writes:

Your column is defined as datetime datatype and you have a default
insert of the getdate() function with is also of datatype datetime
there for your value is not stored in the format '10/12/2004 7:28:02
AM' but instead is similar to: '2004-10-12 07:28:02.000'. I say
similar because it depends on the precision of the milliseconds. What
you need to do is format your where clause value to be compatable with
datetime values:

SELECT auditime
FROM testtable
WHERE convert(varchar(50),auditime,120) = '2004-10-12 07:28:02'

This may not be the best way. Now, it is not that likely that you index a
column like audittime. But assume that there had been an index and you
wanted it to be used. That would happen with the query above. As soon
as you put a column in a expression, SQL Server can no longer use the
index. (Or more precisely, it can no longer seek the index. The index
can still be scanned, that is read all of it.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[manstein]

Erland

It is actually a commonly held myth that columns wrapped in
expressions are not indexable. Sql server 2000, and later, has an
optimization strategy called "Folding" which allows the optimizer to
identify certain types of indexable expressions even when the column is
nested in a function. The optimizer is able to use Folding only in
certain circumstances but it is numeric and date columns were it is
most likely to be used.
As a general rule, i try to answer the specific question that the
poster asked and not the question i want to answer. This really is
rampant on these groups. Often, the first thing a responder will ask
is "why do you want to do it that way?" This is usually follow up with
a (false) claim that i cannot be done the way the poster envisioned.
SQL is an amazingly orthogonal language. (In computer science
parlance, this means that the language is very expressive and its
components can be combined in surprising ways). Many things can be
done that others claim can't. TGear was asking how to match values
EQUAL to a certain date, not over a range of dates. Therefore, for
that reason, my answer is the clearest and most accurate in addressing
his question. Thanks

[Erland Sommarskog]

manstein (jk**********@gmail.com) writes:

It is actually a commonly held myth that columns wrapped in
expressions are not indexable. Sql server 2000, and later, has an
optimization strategy called "Folding" which allows the optimizer to
identify certain types of indexable expressions even when the column is
nested in a function. The optimizer is able to use Folding only in
certain circumstances but it is numeric and date columns were it is
most likely to be used.

Do you have an example? I tried this in both SQL 2000 and SQL 2005:

select * from Orders where OrderID = 11000 - 1
select * from Orders where OrderDate = '19970808'
select * from Orders where OrderID + 1 = 11000
select * from Orders where
convert(varchar(50), OrderDate, 120) = '1997-08-08 00:00:00'

(Database: Northwind)

The first two seek an index to find the result, the other two do
not. The example with OrderID is an apparent case where the optimizer
could be able to figure out how to use the index.

The second example with OrderDate, which is taken from the post earlier
in the thread, is tricker. The optimizer would have to rewrite this into

OrderDate >= '19970808 00:00:00' AND
OrderDate < '19970808 00:00:01'

This is also an exercise with several special cases. Consider:

convert(varchar(50), OrderDate, 113) = '05 jan 2006 00:00:00'

This can be recast as above if these two conditions are fulfilled:

1) The current language has a month "Jan".
2) The database collation is case-insensitive.

(If these are not fulfilled, it's easier, as the condition then is
known to be false.)

Or this:

convert(varchar(16), OrderDate, 120) = '1997-08-08 00:00'

Now the comparison should be cast to an interval of entire minute.

Then instead of a constant, add a column from another table instead,
for an even higher degree of complexity.

Maybe unfolding of columns from expressions will come in some later
version of SQL Server, but it will take a good understanding to know
when it can happen, when it can't, and when it doesn't although it
theoretically could.

My guess is that the feature you are thinking of is constant folding,
so that constant expressions are computed at compile time.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[manstein]

page 488 - 489 The Guru's Guide to SQL Server Architecture and
Internals. Ken Henderson gives this code as an example of folding:

SELECT *
FROM Orders
WHERE ISNULL(Orderdate,getdate())>'2003-04-06 19:55:00.000'

Now, if you check the execution plan, you will see that there is an
index scan on the Orderdate column. Apparently Henderson considers
this an efficient use of an index; hence a good example of optimization
through folding. Similarly, my code also uses a index scan which is
far better than a table scan. Though you do make the distinction above
between index scan and index seek, based on the examination of the
execution plans, your suggestion that my code is in-efficient is
unwarrented. Thanks!

[Alexander Kuznetsov]

>>
Though you do make the distinction above
between index scan and index seek, based on the examination of the
execution plans, your suggestion that my code is in-efficient is
unwarrented.
<<

I have a question: did you set up a big table, build and index, and
actually run your index scan and an index seek? The real life
difference between an index scan and an index seek may be very big...

[Erland Sommarskog]

manstein (jk**********@gmail.com) writes:

page 488 - 489 The Guru's Guide to SQL Server Architecture and
Internals. Ken Henderson gives this code as an example of folding:

SELECT *
FROM Orders
WHERE ISNULL(Orderdate,getdate())>'2003-04-06 19:55:00.000'

Now, if you check the execution plan, you will see that there is an
index scan on the Orderdate column. Apparently Henderson considers
this an efficient use of an index; hence a good example of optimization
through folding. Similarly, my code also uses a index scan which is
far better than a table scan. Though you do make the distinction above
between index scan and index seek, based on the examination of the
execution plans, your suggestion that my code is in-efficient is
unwarrented. Thanks!

As Alexander points out, the difference between an Index Seek and an
Index Scan can be considerable. A table scan may be 1000 pages reads,
an index scan 100 page read, and index seek can be 3 page access.

And if the index in question is the clustered index, the index scan is a
table scan.

Note also that in case of a non-clustered index, there is also the risk
that the optimizer estimates that the number of bookmark lookups will
be so many, that it may go for a clustered index scan anyway.

This is not to say that index scans are always bad. But for the query
at hand, it's possible to write a query that can utilize an index seek,
why it's risky to wrap the column in an expression.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

>>
page 488 - 489 The Guru's Guide to SQL Server Architecture and
Internals. Ken Henderson gives this code as an example of folding: <<

In fact, Ken Henderson is being very cautious when speaking about
folding. He says that folding occurs "under very limited circumstances,
and the rule of thumb is not to rely on it whenever possible. Just
think: in many cases the optimizer does not even have enough time to
consider all the permutations. Because of that limited time, if the
query is complex, folding is less likely to occur. Naturally, for
really really simple queries that fit on a line or two, folding is more
likely to happen. Unfortunately, most production queries are somewhat
bigger...