string date instead datediff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

string date instead datediff

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-10 : 15:42:47

i noticed that when i'am using datediff, then in execution plan is full index scan, but how to make sql query using string date

select ....
from.... where date = '03.03.1983'

when i use this format then in execution plan is index seek

thanks

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-10 : 16:06:37

What does the query with datediff look like?

Sam

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-10 : 16:14:41

END_DATA field is index, and ID in this table is clustered index

query looks like

WHERE DATEDIFF(d,GETDATE(),END_DATA)>0

like this also
SELECT ID
FROM TABLE
WHERE DATEDIFF(d,GETDATE(),END_DATA)>0

Edited by - marconi8 on 05/10/2003 16:17:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-10 : 17:57:13

Basically, whenever you have a function on the left side of a logical test:

DateDiff() > END_DATA

The query optimizer cannot utilize an index because it needs to calculate the expression for each row. Instead, if you rewrite the statement so that the column is on the left side:

END_DATA > GetDate()

It can calculate the right-hand expression once and then use the index to seek for rows that meet that condition. That's why your first expression seeks and the second one scans. Nor do you need to calculate the DateDiff() between the two values; instead, calculate a date value based on GetDate() that you can compare the END_DATA column to.

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-11 : 04:48:28

in field start_data i have value = 11.05.2003 11:36:50

then i make query = select.... where start_data = getdate()

and i get 0 rows, maybe data formats dont match ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-11 : 06:39:28

Don't forget that getdate() gives you the date and time to within a few milliseconds, and getdate() will never return the same value twice. If you need to return rows that match today's date:

SELECT * FROM myTable WHERE start_data >= DateAdd(day, DateDiff(day, 0, getdate()), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources