H2 DB SQL, how to make a variable of the yersterday's date and call it after

Question

Here is example how can i select the day before

SELECT DATEADD(day, -1, CAST(Current_date AS date)) AS YesterdayDate;

Here is my insert, how can I make YesterdayDate to hold a real date in this example?

INSERT INTO dish(id, name, date_added, price, restaurant_id)
    VALUES (14, 'SomeDish', YesterdayDate, 120,2);

Answer 1

You need to use a user-defined variable:

SET @YesterdayDate = CURRENT_DATE - INTERVAL '1' DAY;

INSERT INTO dish(id, name, date_added, price, restaurant_id)
    VALUES (14, 'SomeDish', @YesterdayDate, 120, 2);

These variables aren't persisted and they are visible only in the current session. They are prefixed with the @ symbol.

If you need to use the variable in other sessions, you can create a constant instead:

CREATE CONSTANT YesterdayDate VALUE CURRENT_DATE - INTERVAL '1' DAY;
INSERT INTO dish(id, name, date_added, price, restaurant_id)
    VALUES (14, 'SomeDish', YesterdayDate, 120, 2);

-- After use
DROP CONSTANT YesterdayDate;

Constants are persisted into database and they're visible in all sessions. Their names are used as is.

Values of user-defined variables and constants will not be updated automatically for the next day.

These variables and constants aren't portable across different DBMS.