more query by date - 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)

more query by date

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-14 : 12:58:08

how can I select all users that signed up today

where mydate is = to today (mydate is datetime field)
I want it to return all records from 12...

How would I do this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-14 : 13:01:58

Here's one way:

where datediff(day, mydate, getdate()) = 0

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 02:15:52

>>I want it to return all records from 12...
What do you mean by 12?

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-09-15 : 02:22:01

I meant 12 am --what tg posted is just what I needed.

thansk for your help --

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 02:28:51

If "mydate" is a column in a database table (rather than a variable or somsuch) which contains lots of records it may not be as efficient as

where mydate >= DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)

as this will allow indexes etc. to be used, which I don't think TG's method will (you can replace the bit in blue with a variable if you happen to have one with the right value handy!)

(If mydate can contain dates past today then it would need an upper limit test too)

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-15 : 09:11:51

Nothing wrong with your version Kristen, however:
I think since the my version does no manipulation of the Date column other than using it in a datediff function, sql server can still utilize the index. I did a small test on one of our tables that has an indexed date column just to double check:


select filingid
from	filings_t
where	datediff(day, Filing_dt, getdate()) = 0

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------
|--Index Scan(OBJECT:([<DatabaseName>].[dbo].[Filings_t].[IDX_Filings_filing_dt]), WHERE:(datediff(day, [Filings_t].[Filing_dt], getdate())=0))

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 10:08:52

"sql server can still utilize the index"

I stand corrected, that's worth knowing 'coz its a lot easier to write! Thanks for that.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources