0

I would like to declare a variable to eliminate double entry when entering date information into a 'WHERE' clause. However, the database is read-only and isn't letting me declare anything. This is what I have tried:

SET @begin_date = '2017-12-01'
SET @end_date = '2017-12-30'
WHERE (ship.dateShipped BETWEEN @begin_date AND @end_date) 
OR (fulfill.datefulfilled BETWEEN @begin_date AND @end_date)

The query is simplified for the sake of this post, but is basically what I am trying to do. I have to hand this off to a salesman to run as needed, and want to make it as easy as possible to change the date parameters.

Is there a way to declare variables in this scenario? Or maybe an alternate way to write the 'WHERE' clause so the dates only have to be entered once?

I have also tried:

SELECT @begin_date := '2017-12-01', @end_date := '2017-12-30'......

and also

DECLARE begin_date  INT unsigned DEFAULT '2017-12-01'
DECLARE end_date  INT unsigned DEFAULT '2017-12-30'

to no avail.

Andrew Haponek
  • 3
  • 3
  • 1
    how about inlining the variables? (i.e. `BETWEEN '2017-12-01' AND '2017-12-30'`) –  Dec 14 '17 at 18:48
  • Yes, that works just fine. However, I would like to do this with variables so only 2 dates have to be adjusted (then will update the following variable instances) when changing the range for the report. Trying to make updating the date range as easy as possible for the salesman who will be using this. – Andrew Haponek Dec 14 '17 at 18:56

1 Answers1

0

Your query is simplified almost to the point of gibberish, but something like this should work.

SELECT ...
FROM ....
INNER JOIN (SELECT '2017-12-01' AS beginDate, '2017-12-30' AS endDate) AS dr
ON (ship.dateShipped BETWEEN dr.beginDate AND dr.endDate) 
OR (fulfill.datefulfilled BETWEEN dr.beginDate AND dr.endDate)
...

It really will not let you use @ variables?

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • I am writing the query to pull a report from a Fishbowl Inventory database, and the query would not even run when I was using the @ variables. This join did the trick though. – Andrew Haponek Dec 14 '17 at 20:01