Return Data from Yesterday Only

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)

Return Data from Yesterday Only

Author

Topic

jmiskey
Starting Member

15 Posts

Posted - 2007-06-01 : 09:53:19

I have a query that I am writing, and I am trying to limit my results to return everything that was added yesterday (regardless of time). Since I need to use this query every day, I would like it to be dynamic. So I am trying to make use of GETDATE() instead of hard-coding my dates.

Here is the WHERE clause I have tried:

WHERE INSERTTIMESTAMP>=DATEADD("D",-1,GETDATE()) AND INSERTTIMESTAMP<GETDATE()

This does not seem to work properly, as it is returning stuff from today also. I am pretty sure that the problem is that GETDATE() has a time component. I think if I could tell the clause to ignore the time component, then it would work.

How do I do this?

Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-01 : 10:32:43

see: http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

using the DateOnly() function you can simply write:

where Inserttimestamp >= dbo.DateOnly(getdate()-1) and Inserttimestamp < dbo.DateOnly(getdate())

- Jeff
http://weblogs.sqlteam.com/JeffS

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-01 : 10:35:23

Use this to efficiently truncate your datetime values:


WHERE	INSERTTIMESTAMP>=dateadd('d', datediff('D', 0, GetDate())-1, 0)
	AND INSERTTIMESTAMP<dateadd('d', datediff('D', 0, GetDate()), 0)

e4 d5 xd5 Nf6

jmiskey
Starting Member

15 Posts

Posted - 2007-06-01 : 11:01:01

Thanks Jeff and Blindman, they both work (once I removed the single quotes from around the d's in Blindman's formula).

Jeff, I agree with the sentiment in the link you provided, why did they omit these functions from T-SQL? It shouldn't be that complicated to get that date.

One question, is one solution more efficient than the other? Is it more expensive to use a UDF?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-01 : 11:09:59

No worries.

The rumor is that next version of SQL server is going to provide different data types for Date and Time data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-01 : 11:16:49

It probably is slightly more expensive, but the cost is minimal, and you get much clearer code. The bottleneck is always going to be retrieving data from the database, not doing simple one-time calculations.

You can ensure that the UDF is called just once by using and setting variables before the sql statement; i.e.,

declare @mindate datetime, @maxDate datetime
set @minDate = ...
set @maxDate = ...

select .. where date between @minDate and @maxDate

- Jeff
http://weblogs.sqlteam.com/JeffS

jmiskey
Starting Member

15 Posts

Posted - 2007-06-01 : 11:18:46

harsh,

Thanks, that's good to know, but that probably won't help me for next 10 years! It is 2007 and we are still using SQL Server 2000 instead of 2005. My company updgrades at the pace of a turtle (I guess "slow and steady" would be our motto)...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources