Need to find 'yesterday'

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • teddarr
    New Member
    • Oct 2006
    • 143

    #1

    Need to find 'yesterday'

    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
    New Member
    • Jul 2007
    • 3

    #2
    Originally posted by 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


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

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

    Comment

    • teddarr
      New Member
      • Oct 2006
      • 143

      #3
      Yes, I always want yesterdays date

      BUT

      It can't really be that easy can it?

      Comment

      • camscott
        New Member
        • Jul 2007
        • 3

        #4
        Originally posted by teddarr
        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

        Comment

        • Infide
          New Member
          • Jul 2007
          • 28

          #5
          Originally posted by camscott
          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,cre ationdate) = datepart(dd,dat eadd(dd,-1,getdate())
          and datepart(mm,cre ationdate) = datepart(mm,dat eadd(dd,-1,getdate())
          and datepart(yyyy,c reationdate) = datepart(yyyy,d ateadd(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:
          Code:
          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_DateToJu stAfterMidnight (dateadd(dd,-1,getdate()))

          Comment

          Working...