HAFF HAFF - 15 days ago 9
MySQL Question

Declaring Variable in db2 mysql

BEGIN
DECLARE @STDATE DATE DEFAULT '2015-08-19';
DECLARE @ENDATE DATE DEFAULT '2016-08-19';
SELECT * FROM MYTABLE
WHERE DATE BETWEEN @STDATE AND @ENDATE
END


I'm trying to execute a query on db2 using sql to allow retrieval of data and calculation of results for a period of time, defined by Start Date and End Date. The Start Date and the End Date should be made into variables, or parameters.

When I'm trying to execute this statements an error of unexpected token

declare @stdate is thrown also tried using
create variable sdate char(10) DEFAULT '2015-08-19';
create variable edate char(10)DEFAULT '2016-08-19';
SELECT * FROM MYTABLE
WHERE DATE BETWEEN @SDATE AND @EDATE
END


i made changes according to the suggestion of gordon i executed the query

BEGIN
DECLARE v_stdate date;
DECLARE v_endate date;
SET v_date=date('2016-01-01');
SET v_endate= date ('2016-04-03');
SELECT * FROM MYTABLE
WHERE DATE BETWEEN v_stdate AND v_endate
END
the error of unexpected token is removed
but then i am getting another error
[enter image description here][it says unexpected 'as'] then i commented the line
its throwing another sql error saying unexpected error 'from' at the select * from my table

please help

Answer

Possibility if you are on iseries (old AS400) is

--creation des variables
CREATE OR REPLACE VARIABLE yourlib.sdate date DEFAULT '2015-08-19';
CREATE OR REPLACE VARIABLE yourlib.edate date DEFAULT '2016-08-19';

-- your query with variables used
SELECT * FROM yourlib.yourtable
WHERE yourdate  BETWEEN yourlib.sdate AND  yourlib.edate;

-- free variable
drop VARIABLE yourlib.sdate ;
drop VARIABLE yourlib.edate ;

It is best to create your variables in the qtemp Library, except if you want keep this variables as global for other scripts.