none
incorporate yesterday's date, so I can schedule this!

    Question

  • Hello all,

    struggling SQL user. I have researched and tried all kinds of Cast, Convert, DateAdd, etc...

    EVERY time I try, I get SQL Syntax errors.  

    Hate to throw the whole query in here, but apparently when I try to insert small things, i blow it all up.

    In the WHERE clause, I can also refer to a column formatted as DateTime, with the same name as the key, but using '...TIME' instead of '...Key' - 

    I've even tried system time

    Where convert(int,(convert(varchar(8),sysdatetime()-1,112)) ) 

    to no avail.

    here's the whole thing...

    Select QB.vw_PocketAccess.PocketAccessDateKey As Date,
      QB.vw_TransactionType.TransactionDescription As Trans,
      QB.vw_PocketAccess.TransactionQuantity As Qty,
      QB.vw_User.UserName As [User Name],
      QB.vw_PocketAccess.PocketAccessDateTime As [Time Stamp],
      QB.vw_Device.DeviceName As Station
    From QB.vw_PocketAccess
      Inner Join QB.vw_TransactionType On QB.vw_PocketAccess.TransactionTypeKey =
        QB.vw_TransactionType.TransactionTypeKey
      Inner Join QB.vw_User On QB.vw_PocketAccess.UserKey = QB.vw_User.UserKey And
        QB.vw_PocketAccess.DataStoreKey = QB.vw_User.DataStoreKey
      Inner Join QB.vw_Item On QB.vw_PocketAccess.ItemKey = QB.vw_Item.ItemKey And
        QB.vw_PocketAccess.DataStoreKey = QB.vw_Item.DataStoreKey
      Inner Join QB.vw_Device On QB.vw_Item.DataStoreKey = QB.vw_Device.DataStoreKey
        And QB.vw_PocketAccess.DeviceKey = QB.vw_Device.DeviceKey
      Inner Join QB.vw_ItemClass On QB.vw_PocketAccess.DataStoreKey =
        QB.vw_ItemClass.DataStoreKey And QB.vw_Item.ItemClass =
        QB.vw_ItemClass.ItemClass And
        QB.vw_Item.ClientKey = QB.vw_ItemClass.ClientKey
    Where QB.vw_PocketAccess.PocketAccessDateKey Between 20190414 And 20190414
      And QB.vw_TransactionType.TransactionDescription = 'refill' And
      QB.vw_Device.DeviceName Not Like 'UHM%' And QB.vw_PocketAccess.DataStoreKey =
      196

    Monday, April 15, 2019 3:27 PM

Answers

  • What you actually want is:

    Where QB.vw_PocketAccess.PocketAccessDateKey BETWEEN CAST(CONVERT(VARCHAR(20), dateadd(day, -1,GETDATE()),112) AS INT) AND CAST(CONVERT(VARCHAR(20),getdate(),112) AS INT)
    

    • Marked as answer by Zymurgist Monday, April 15, 2019 4:37 PM
    Monday, April 15, 2019 4:35 PM
    Moderator

All replies

  • Where QB.vw_PocketAccess.PocketAccessDateKey >='20190414' And QB.vw_PocketAccess.PocketAccessDateKey <'20190415'

    Monday, April 15, 2019 3:44 PM
    Moderator
  • Dynamic:

    Where QB.vw_PocketAccess.PocketAccessDateKey >=dateadd(day, datediff(day,0,getdate())-1,0) 
    And QB.vw_PocketAccess.PocketAccessDateKey <dateadd(day, datediff(day,0,getdate()),0)

    Monday, April 15, 2019 3:46 PM
    Moderator
  • Yep, Dynamic was what I was looking for. - thanks !

    Well, I get a new error message with this one ...

    "statement must contain a valid date filter"


    • Edited by Zymurgist Monday, April 15, 2019 4:11 PM
    Monday, April 15, 2019 4:10 PM
  • What is your code looking now?
    Monday, April 15, 2019 4:26 PM
    Moderator
  • What you actually want is:

    Where QB.vw_PocketAccess.PocketAccessDateKey BETWEEN CAST(CONVERT(VARCHAR(20), dateadd(day, -1,GETDATE()),112) AS INT) AND CAST(CONVERT(VARCHAR(20),getdate(),112) AS INT)
    

    • Marked as answer by Zymurgist Monday, April 15, 2019 4:37 PM
    Monday, April 15, 2019 4:35 PM
    Moderator
  • NO errors on that one, Tom - and without a specific date reference, (I just ran it) - I got Yesterday !

    Thank you so very much !!!

    Monday, April 15, 2019 4:39 PM
  • You should post your table DDL. It seems your PocketAccessDateKey is int column.

     Even Tom's solution has no error but it is correct with the between ... and. You should use  the below to retrieve yesterday' rows.

    where PocketAccessDateKey>= CAST(CONVERT(VARCHAR(8), dateadd(day, -1,GETDATE()),112) AS INT) 
     and PocketAccessDateKey <CAST(CONVERT(VARCHAR(8),getdate(),112) AS INT)

    An example for this issue:

    create table test1 (id int, PocketAccessDateKey  int)
    
    --including today's
    insert into test1 values (1,20190414),(3,20190413) ,(3,20190415)
    select  id, PocketAccessDateKey  from test1
    where PocketAccessDateKey between  CAST(CONVERT(VARCHAR(8), dateadd(day, -1,GETDATE()),112) AS INT) 
     and  CAST(CONVERT(VARCHAR(8),getdate(),112) AS INT)
    
    
     --correct
     select  id, PocketAccessDateKey  from test1
    where PocketAccessDateKey>= CAST(CONVERT(VARCHAR(8), dateadd(day, -1,GETDATE()),112) AS INT) 
     and PocketAccessDateKey <CAST(CONVERT(VARCHAR(8),getdate(),112) AS INT)
    
    drop table test1
    

    Monday, April 15, 2019 5:22 PM
    Moderator