Using declare in SQL query with a join in to get date time between for yesterday

Question

I am trying to use this query to bring back yesterdays data, ---- represents columns/tables

DECLARE @today date = GETDATE()

SELECT * 
FROM ------
LEFT JOIN -----
ON ------ = ----
WHERE ------.DATE_TIME   >= @today
AND -----.DATE_TIME   <  DATEADD(DAY, -1, @today)

I have used this DECLARE STATEMENT before and it worked but never with a join.

I am getting this error back error

Error text:

incorrect syntax near "DECLARE": line 1 col 1 (at pos 1) OpenSQLExceptionCategories: [NON_TRANSIENT, SYNTAX_ERROR]

Yesterdays data, is there another way of doing this?

Answer 1

In addition to the syntax issues, it looks like the boundaries were backwards. No value will ever be both after today and before yesterday.

Try this:

SELECT * 
FROM ------
LEFT JOIN -----
    ON ------ = ----
WHERE ------.DATE_TIME >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
    AND -----.DATE_TIME < CAST(GETDATE() AS DATE) 

The above is correct for a Microsoft SQL Server backend. The error message also indicates SAP is talking to... something else. In order to write this correctly you need to know what backend database type you're using behind SAP.

After some digging, I finally found this page, which suggests MaxDB as a possibility. If that is correct, the documentation suggests the code should look like this:

SELECT * 
FROM ------
LEFT JOIN -----
    ON ------ = ----
WHERE ------.DATE_TIME >=  subdate(date(now()),1)
    AND -----.DATE_TIME <  date(now())