none
SQL Query for yesterday's data

    Question

  •  

    Hello,

    Could any one of you please help me to write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i dont want. everyday i need to search the data from yesterday whole day. Any help will be appreciated,

    Thanks,

    Narayan

    Tuesday, December 02, 2008 8:23 PM

Answers

  • Something like this should work.  I am subtracting one date from getdate and converting the date so the time will not matter.

     

    Select *

    From Table1

    WHERE Convert(varchar(10),Table1.DteColumn,101) = Convert(varchar(10),getdate() -1,101)

     

    Tuesday, December 02, 2008 8:33 PM
  • select * from MyTable

    where MyDate >=dateadd(d,datediff(d,0, getdate()),-1) and MyDate<dateadd(d,datediff(d,0, getdate()),0)

    Tuesday, December 02, 2008 8:50 PM
  • Or simple one

    Code Snippet

     

    SELECT *

    FROM test

    WHERE datediff( d,date,getdate()) = 1

     

     

     

    Avoid doing the CONVERT of datetime columns in where clause.

    Wednesday, December 03, 2008 5:15 AM
    Moderator
  • Code Snippet

    SELECT * FROM yourTable

    WHERE dtColumn >=CONVERT(nvarchar(10),getdate()-1,101)

    AND dtColumn < CONVERT(nvarchar(10),getdate(),101)

     

     

     

    Tuesday, December 02, 2008 8:46 PM
    Moderator

All replies

  • Something like this should work.  I am subtracting one date from getdate and converting the date so the time will not matter.

     

    Select *

    From Table1

    WHERE Convert(varchar(10),Table1.DteColumn,101) = Convert(varchar(10),getdate() -1,101)

     

    Tuesday, December 02, 2008 8:33 PM
  • Code Snippet

    SELECT * FROM yourTable

    WHERE dtColumn >=CONVERT(nvarchar(10),getdate()-1,101)

    AND dtColumn < CONVERT(nvarchar(10),getdate(),101)

     

     

     

    Tuesday, December 02, 2008 8:46 PM
    Moderator
  • select * from MyTable

    where MyDate >=dateadd(d,datediff(d,0, getdate()),-1) and MyDate<dateadd(d,datediff(d,0, getdate()),0)

    Tuesday, December 02, 2008 8:50 PM
  • Or simple one

    Code Snippet

     

    SELECT *

    FROM test

    WHERE datediff( d,date,getdate()) = 1

     

     

     

    Avoid doing the CONVERT of datetime columns in where clause.

    Wednesday, December 03, 2008 5:15 AM
    Moderator
  • THanks a lot all of you for these queries.

     

    Regards,

    Narayan

     

    Wednesday, December 03, 2008 2:21 PM
  • I quite like this method: very clean (even if I do say so myself) Wink

     

    Code Snippet

    DECLARE @today datetime

        SET @today = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)

     

    SELECT *

    FROM   dbo.mytable

    WHERE  mydate < @today

    AND    mydate >= DateAdd(dd, -1, @today)

     

     

     

    Wednesday, December 03, 2008 4:43 PM
    Answerer
  • Hi Denis,

    I just wanted to thank you for a clear and precise snippet that solved a problem for me.

    SELECT
       OriginalEvent,
       Tag,  
       PValue,
       Units,
       ConditionName,
       AckComment
    FROM
       dbo.EventLog
    WHERE
       dbo.EventLog.OriginalEvent >=dateadd(d,datediff(d,0, getdate()),-1) and  dbo.EventLog.OriginalEvent<dateadd(d,datediff(d,0, getdate()),0) and
    dbo.EventLog.ConditionActive=1 and
    dbo.EventLog.Tag LIKE '%LTCBMS\NC%' and
    dbo.EventLog.Acked=0

    Tuesday, June 16, 2009 6:58 PM
  • Hi Want2Learn,

    Just to adjust your where clause slightly:

    WHERE
       dbo.EventLog.OriginalEvent >=dateadd(d,datediff(d,0, getdate()) -1, 0)

    The reason for this is that the the second parameter of DateAdd is the number of units to add/remove from a date value, the 3rd parameter is the date itself.

    If you ever use the smalldatetime data type, the query as you wrote it will fail, because -1 will cause an arithmetic overflow when converting to datetime, whereas zero will not!


    To help illustrate what I mean:

    DECLARE @datetime      datetime
          , @smalldatetime smalldatetime
    
    BEGIN TRY
      SET @datetime = -1
      SELECT @datetime
    END TRY
    BEGIN CATCH
      SELECT Error_Message()
    END CATCH
    
    BEGIN TRY
      SET @smalldatetime = -1
      SELECT @smalldatetime
    END TRY
    BEGIN CATCH
      SELECT Error_Message()
    END CATCH

    George
    Wednesday, June 17, 2009 12:48 PM
    Answerer
  • This is BRILLIANT, thanks Mangal!
    Wednesday, June 09, 2010 4:41 PM
  • Assuming you may have an index on Date field, Datediff solution will not allow to use it since the query will be not sargable. 
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, June 09, 2010 11:06 PM
    Moderator
  • I like this very much...

    I'm not a advanced user of SQL nor a beginner.

    I have a view for a table to show the record for the present day which is:

    SELECT     TOP (100) PERCENT UpdateTime, UserName, Application, ServerName, SessionState, LogonTime
    FROM         dbo.CXA_UserSessions
    WHERE     (UpdateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
    ORDER BY UpdateTime DESC

    I would like to have the same thing but for all records from last 7 days.

     

    What would you suggest???

    Tks


    Team is a group in which members work together to achieve a common goal.
    Wednesday, October 26, 2011 3:36 PM
  • Try:

     

    SELECT     UpdateTime, UserName, Application, ServerName, SessionState, LogonTime
    FROM         dbo.CXA_UserSessions
    WHERE     UpdateTime >= DATEADD(day, DATEDIFF(day, '19000108', CURRENT_TIMESTAMP), '19000101')
    ORDER BY UpdateTime DESC
    

    BTW, ORDER BY is ignored in views (unless you specify TOP (N) where N is less than 100 percent).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, October 26, 2011 3:46 PM
    Moderator