April 1, 2008 at 10:49 am
I am sure this is common and simple. I've tried many variations using DATEPART OR DATEADD to no avail. This current script gave me yesterday and today's orders. In using BETWEEN and AND on GETDATE didn't help, I received the same population.
WHEREprocessDate > GETDATE()-1
or
WHERE ProcessDate BETWEEN GETDATE()-1 AND GETDATE()
or
WHERE ProcessDate > GETDATE()-1
AND ProcessDate < GETDATE()
All help appreciated.
April 1, 2008 at 11:05 am
WHERE CONVERT(VARCHAR,MyDateColumn,101) =
CONVERT(VARCHAR,DATEADD(dd,-1,GETDATE()),101)
April 1, 2008 at 11:07 am
getdate()-1 is not "yesterday", but instead a specific time yesterday. So, if processDate is '3/31/2008' it will not be between '3/31/2008 13:01:00' and '4/1/2008 13:01:00'.
you'll need to truncate getdate() to the start of today.
declare @yesterday smalldatetime
set @yesterday = dateadd( day, datediff( day, 0, getdate()) - 1, 0)
select ...
where processDate >= @yesterday
April 1, 2008 at 11:11 am
I like Antonio method more than the one I posted because it reduces the additional overhead of converting dates.
April 1, 2008 at 12:59 pm
Antonio and Adam --Thanks for your help. I worked out a solution mixing your ideas. Antonio's script still displayed todays data with the >=.
So, with a little modification:
DECLARE @yesterday smalldatetime
,@today smalldatetime
SET@today = CONVERT(VARCHAR,GETDATE(),10)
SET@yesterday = dateadd(day, datediff(day,0,getdate()) - 1, 0)
--PRINT@yesterday
--PRINT@today
SELECT
OrderNumber
FROM[dbo].[Order]
WHERE
ProcessStatus = 's'
--AND CONVERT(VARCHAR,ProcessDate,101) = CONVERT(VARCHAR,DATEADD(dd,-1,GETDATE()),101)
AND ProcessDate BETWEEN @yesterday AND @today
Any additional thoughts or questions? Thanks
April 1, 2008 at 3:28 pm
Change:
ProcessDate BETWEEN @yesterday AND @today
to:
ProcessDate >=@yesterday AND processdate < @today
Otherwise, you'll run the risk of getting data from midnight last night (technically today) in the mix.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 10:07 am
Why not just use
WHERE
DATEDIFF(day,processdate,getdate()) = 1
??
April 3, 2008 at 10:15 am
Why not just use
WHERE
DATEDIFF(day,processdate,getdate()) = 1
Good post. To answer the question of why I say, the simplest solution is usually the least apparent. 🙂
April 3, 2008 at 10:33 am
Perhaps I screwed up my index here, but I get a table scan from each of these
create table datevalues (
dates datetime,
somevals integer )
create index bobsyouruncle on datevalues(dates DESC)
create statistics suesyouraunt on datevalues(dates)
declare @dateval datetime
declare @values integer
declare @step integer
declare @saveme integer
set @step = 0
set @dateval = '03/01/08'
while @dateval <= getdate()
BEGIN
set @values = 1
WHILE @values < 10
BEGIN
set @saveme = @values + 10 * @step
insert into datevalues values(@dateval,@saveme)
set @values = @values + 1
END
set @step = @step + 1
set @dateval = dateadd(day,1,@dateval)
END
select somevals from datevalues
where dates between '03/10/08' and '03/12/08'
select somevals from datevalues
where dates >= '03/10/08' and dates <= '03/12/08'
select somevals from datevalues
where datediff(day,dates,getdate()) = 1
select somevals from datevalues
where convert(varchar,dates,101) = convert(varchar,dateadd(dd,-1,getdate()),101)
Both my estimated and my actual execution plans are coming out as Table Scans.
April 3, 2008 at 10:53 am
Try it with more rows! 😉
With your code, the first two selects CAN use an normal index, the second two cannot. Unfortunately, though with the only 300 or so records in your example, it will table scan. Increase your row count to 100k, or a million, and you'll see the difference.
April 3, 2008 at 12:21 pm
Ok... after pushing up to 100K rows I see your point.
Is there a way to use DATEDIFF and get an Index Seek? I'm a little sad that my favorite little function seems to always trigger a table scan. 🙁
April 7, 2008 at 10:19 am
Thanks all for the imput. I loved the thread and shared the complexity with fellow staff.
For my purposes, The DATEDIFF works well for me because I am rarely calling on more than several hundred rows at best; DATEDIFF works perfectly fine.
April 7, 2008 at 12:03 pm
mtassin (4/3/2008)
Ok... after pushing up to 100K rows I see your point.Is there a way to use DATEDIFF and get an Index Seek? I'm a little sad that my favorite little function seems to always trigger a table scan. 🙁
Nope.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 12:05 pm
David Hart (4/7/2008)
Thanks all for the imput. I loved the thread and shared the complexity with fellow staff.For my purposes, The DATEDIFF works well for me because I am rarely calling on more than several hundred rows at best; DATEDIFF works perfectly fine.
Just keep in mind that it won't scale well. If the database gets more rows, more users, etc., you might have to re-write that piece of code.
One thing to keep in mind is that a scalable solution (like the solutions that can use indexes) might save you a lot of time and frustration later on, and doesn't cost you a lot more time now, while a non-scalable solution might save you a little time now, but can cause major headaches and cost a lot of time later.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply