Use 'Today' in SP - 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

Transact-SQL (2000)

Use 'Today' in SP

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-09-22 : 04:58:24

Is there a system word that can be used for Today in an sp ?

eg

BD_DateRequired > 'Today'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 04:59:45

If you mean Today's Date then GetDate()

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-22 : 05:00:15

getdate()

eg BD_DateRequired > GETDATE()

Duane.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-09-22 : 05:01:10

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 05:05:00

But Actually you need to use this

Select Columns from yourTable where DateDiff(day,GETDATE(),BD_DateRequired)>0

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 05:05:19

Or

BD_DateRequired > DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1)

which is midnight (border between today and tomorrow), and will not include any values of BD_DateRequired that contains a TIME during today

Change 1 to 0 if you need the midnight border between yesterday and today

Kristen

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 05:06:25

"where DateDiff(day,GETDATE(),BD_DateRequired)>0"

won't use any available index on BD_DateRequired, will it Maddy?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 05:10:36

I dont know Kris. You have to tell me
I know usage of Cast and Convert functions stops usage of index. Will that apply for DateDiff also?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-22 : 05:17:34

it makes an index scan:


select * from orders
where orderdate < getdate()
select * from orders
where DateDiff(day, GETDATE(), orderdate)<0

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 08:31:12

In Northwind both those use the PK though, probably because the table is too small.

So I decided to have a go with a table of my own with has 86,444 rows, and an Index on a Datetime column, and select about 1,500 rows. There is a difference between IndexSeek and IndexScan:

Total rows in MyTable = 86,444

SELECT COUNT(*)
FROM dbo.MyTable
WHERE MyColumn < CONVERT(datetime, '20050805')

|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
|--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
|--Index Seek(OBJECT:([MyDB].[dbo].[MyTable].[MyDateIndex]), SEEK:([MyTable].[MyColumn] < 'Aug 5 2005 12:00AM') ORDERED FORWARD)

Table 'MyTable'. Scan count 1, logical reads 7

Result:
-----------
1448

SELECT COUNT(*)
FROM dbo.MyTable
WHERE DateDiff(day, CONVERT(datetime, '20050805'), MyColumn)<0

|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1006])))
|--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
|--Index Scan(OBJECT:([MyDB].[dbo].[MyTable].[MyDateIndex]), WHERE:(datediff(day, 'Aug 5 2005 12:00AM', [MyTable].[MyColumn])<0))

Table 'MyTable'. Scan count 1, logical reads 196

Result:
-----------
1448

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-22 : 09:06:31

So BD_DateRequired > DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1) is better?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-22 : 09:12:38

"better"

Dunno. In my test its faster

However, there are also, IIRC, issues where if you use

WHERE MyColumn > @MyDateParameter

in an SProc the query optimiser may miss the usefulness of a date index altogether, so I guess you could be worse off.

So "I dunno" is my answer

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-23 : 04:45:42

yep, definitely faster

ever since i read that tip on one of the posts a very long time ago here at sqlteam, i've modified all objects that have this type of conditions

thanks y'all

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 08:18:03

What are you saying here Jen please?

You've changed all instances of

WHERE SomeFunction(MyIndexedColumn) > @xxxx

to something like

WHERE MyIndexedColumn > SomeFunction(@xxxx)

or that you have made specific changes where

WHERE MyColumn > @Parameter

may be handled sub-optimally by the optimiser when it is an SProc and the value of the @Parameter is not exposed to the optimiser?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources