Retrieve yesterday's records using SQL View

Question

Hi I'm trying to retrieve yesterday tranfer row using date. when creating the view. I filtered the date to be retrieve yesterday value but it keeps coming with null value.

the code is WHERE (TranDate=DATEADD(d,-1,GETDATE())

Any help guys?

View screenshot SQL_View

Answer 1

Try this:

--DROP TABLE #PartTran;

CREATE TABLE #PartTran
    (
      PartNum INT
    , WareHouseCode INT
    , TranDate DATE
    , TranQty INT
    );

INSERT  INTO #PartTran
        ( PartNum, WareHouseCode, TranDate, TranQty )
VALUES  ( 1  -- PartNum - int
          , 101  -- WareHouseCode - int
          , GETDATE() - 1  -- TranDate - date
          , 0  -- TranQty - int
          ),
        ( 1  -- PartNum - int
          , 102  -- WareHouseCode - int
          , GETDATE() - 2  -- TranDate - date
          , 5  -- TranQty - int
          ),
        ( 2  -- PartNum - int
          , 101  -- WareHouseCode - int
          , GETDATE()  -- TranDate - date
          , 3  -- TranQty - int
          ),
        ( 4  -- PartNum - int
          , 105  -- WareHouseCode - int
          , GETDATE() - 1  -- TranDate - date
          , 2  -- TranQty - int
          ),
        ( 6  -- PartNum - int
          , 102  -- WareHouseCode - int
          , GETDATE() - 5  -- TranDate - date
          , 0  -- TranQty - int
          ),
        ( 1  -- PartNum - int
          , 101  -- WareHouseCode - int
          , GETDATE()  -- TranDate - date
          , 1  -- TranQty - int
          );

SELECT  PartNum
      , WareHouseCode
      , TranDate
      , TranQty
FROM    #PartTran
WHERE   TranDate = DATEADD(DAY, -1, CAST(GETDATE() AS DATE));