Need to find 'yesterday'

[teddarr]

I'm writing a stored procedure that will gather a few attributes and put those on a report of events that happened yesterday. I'm having trouble with the date. How do I put this in a WHERE clause to select the events that occured yesterday.

Here's what I have so far:

SELECT d.ToName, d.CreationTime, df.NumPages
FROM documents d INNER JOIN docfiles df ON
d.docfileDBA = df handle
WHERE d.CreationTime >= (yesterday's date)

Thanks

[camscott]

I'm writing a stored procedure that will gather a few attributes and put those on a report of events that happened yesterday. I'm having trouble with the date. How do I put this in a WHERE clause to select the events that occured yesterday.

Here's what I have so far:

SELECT d.ToName, d.CreationTime, df.NumPages
FROM documents d INNER JOIN docfiles df ON
d.docfileDBA = df handle
WHERE d.CreationTime >= (yesterday's date)

Thanks

WHERE d.CreationTime >= (GETDATE() - 1)

This, of course, assumes that you ALWAYS want yesterday's date...

[teddarr]

Yes, I always want yesterdays date

BUT

It can't really be that easy can it?

[camscott]

Yes, I always want yesterdays date

BUT

It can't really be that easy can it?

Yep...it really is that easy...try it...

Here is my result from the query SELECT GETDATE()-1:

2007-07-05 07:35:24.480

[Infide]

Yep...it really is that easy...try it...

Here is my result from the query SELECT GETDATE()-1:

2007-07-05 07:35:24.480

Most likely though he will want the entire date range for yesterday. GETDATE() - 1 will exclude all times from the current time to midnight.

For example, a result that had a date of 7-5-2007 7:35:23 would not be returned in your query.

Another solution would be either.
Select *
from table
where DATEPART(dd,creationdate) = datepart(dd,dateadd(dd,-1,getdate())
and datepart(mm,creationdate) = datepart(mm,dateadd(dd,-1,getdate())
and datepart(yyyy,creationdate) = datepart(yyyy,dateadd(dd,-1,getdate())

Not very pretty but it will give you all creation dates for yesterday, not just the creation dates after the random time generated by getdate().

You could also create a scalar function that returns the date to just after midnight.

For example:

CREATE  FUNCTION dbo.fn_DateToJustAfterMidnight(@Date DATETIME)
returns datetime
as
begin
        declare @returnDate datetime, 
                @year int, 
                @month int, 
                @day int
 
    set @year = DATEPART(yyyy, @Date)
        set @month = DATEPART(mm, @Date)
        set @day = DATEPART(dd, @Date)
 
    if (@Date is not null) begin
       set @returnDate = cast(convert(varchar(4), @year) + '-' + convert(varchar(2), @month) + '-' + convert(varchar(2), @day) AS DATETIME)
        end
 
    return @returnDate
end
 

then you would just write:

Select *
From Table
Where CreationDate <= dbo.fn_DateToJustAfterMidnight(dateadd(dd,-1,getdate()))