How to get date variable in query

Get Help
1

I have below query SELECT cl.name, SUM(od.product_price * od.product_quantity / o.conversion_rate) as orderSum, SUM(od.product_quantity) as orderQty, AVG(od.product_price / o.conversion_rate) as priveAvg
FROM orders o
STRAIGHT_JOIN order_detail od ON o.id_order = od.id_order
LEFT JOIN product p ON p.id_product = od.product_id
INNER JOIN product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN category_lang cl ON (product_shop.id_category_default = cl.id_category AND cl.id_lang = 1 AND cl.id_shop = 1 )
WHERE o.valid = 1
AND o.invoice_date BETWEEN ‘2019-08-01 00:00:00’ AND '2019-08-01 23:59:59’
AND o.id_shop IN (1)
GROUP BY product_shop.id_category_default

Here the date ‘2019-08-01 00:00:00’ AND '2019-08-01 23:59:59’ I need to get from the invoice_date table. How I can get this.

2

Hi @ekabyte
I don’t understand what the problem is. Can you describe it in a different way?
Sounds like you want to use Field Filter, but you cannot use table aliases in that case.
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

3

date mentioned above you can see static. I need use as variable and should be able to use it in filter. If i select today’s date i should be able to see today’s statistics. If i select yesterday date i should be able to see yesterday’s statistics.

4

@ekabyte
Did you look at the documentation?
You should just do something like this and connect the filter variable as Field Filter:

WHERE orders.valid = 1
AND orders.id_shop IN (1)
[[AND {{invoice_date}}]]
5

@ flamber Thank you very much. I have already checked this document and count not found resolution. I will check once I get access server on Monday.