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?

(1) Don't paste images when pasting text is better (I added the error text for you), and (2) Add your RDBMS as a tag for this question. — Marc, Jun 20 '23 at 15:04
Also, why do you need the `declare` statement at all? Why can't you just use `GETDATE()` within the query itself? — Marc, Jun 20 '23 at 15:07
Yes, what Relational Database Management System are you using? Oracle? Microsoft SQL Server? MySQL? Add a tag to your question, because they don't all use the same syntax, so a reliable answer might depend on that. — Marc, Jun 20 '23 at 15:08
My version (SAP MII) SQL transaction doesn't have that function or I can't get it to work. — Dbrooks53, Jun 20 '23 at 15:09

Dums · Answer 1 · 2023-06-20T15:21:27.977

0

The error you're encountering is because the DECLARE statement is not allowed in the context you're using it. In SQL Server, you cannot use the DECLARE statement directly in a query. It is typically used within a stored procedure, function, or batch script.

To retrieve yesterday's data, you can use a different approach without the need for a DECLARE statement. Here's an alternative query that you can use:

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

edited Jun 20 '23 at 15:21

answered Jun 20 '23 at 15:20

Dums

1
3

That gives me this error : invalid name of function or procedure: GETDATE: line 6 col 45 (at pos 188) OpenSQLExceptionCategories: [NON_TRANSIENT, DB_OBJECT_UNKNOWN] – Dbrooks53 Jun 20 '23 at 15:28
It seems that you are using a database system where the GETDATE() function is not available. Try different method : - MySQL : WHERE ------.DATE_TIME >= CURDATE() AND -----.DATE_TIME < CURDATE() - INTERVAL 1 DAY - Oracle : WHERE ------.DATE_TIME >= TRUNC(SYSDATE) AND -----.DATE_TIME < TRUNC(SYSDATE) - 1 – Dums Jun 20 '23 at 15:33
Both don't work, thanks anyway. – Dbrooks53 Jun 20 '23 at 15:49

Joel Coehoorn · Answer 2 · 2023-06-20T15:59:53.157

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())

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

2 Answers2