Declare a variable in RedShift

Question

SQL Server has the ability to declare a variable, then call that variable in a query like so:

DECLARE @StartDate date;
SET @StartDate = '2015-01-01';

SELECT *
FROM Orders
WHERE OrderDate >= @StartDate;

Does this functionality work in Amazon's RedShift? From the documentation, it looks that DECLARE is used solely for cursors. SET looks to be the function I am looking for, but when I attempt to use that, I get an error.

set session StartDate = '2015-01-01';
 [Error Code: 500310, SQL State: 42704]  [Amazon](500310) Invalid operation: unrecognized configuration parameter "startdate";

Is it possible to do this in RedShift?

Answer 1

Slavik Meltser's answer is great. As a variation on this theme, you can also use a WITH construct:

WITH tmp_variables AS (
SELECT 
   '2015-01-01'::DATE AS StartDate, 
   'some string'      AS some_value,
   5556::BIGINT       AS some_id
)

SELECT *
FROM Orders
WHERE OrderDate >= (SELECT StartDate FROM tmp_variables);