Setting Big Query variables like mysql

Question

what is the bigquery equivalent to mysql variables like?

SET @fromdate = '2014-01-01 00:00:00',  -- dates for after 2013 @todate='2015-01-01 00:00:00',  @bfromdate = '2005-01-01 00:00:00', -- dates for before 2013 @btodate = '2005-01-01 00:00:00',  @achfromdate  = '2013-01-01 00:00:00', -- dates for ACH without submit time in 2013 @achtodate  = '2013-01-01 00:00:00',  @currency="USD"; 

Answer 1

You could use a WITH clause. It's not ideal, but it gets the job done.

-- Set your variables here WITH vars AS (   SELECT '2018-01-01' as from_date,          '2018-05-01' as to_date )  -- Then use them by pulling from vars with a SELECT clause SELECT * FROM   your_table  WHERE  date_column BETWEEN           CAST((SELECT from_date FROM vars) as date)           AND           CAST((SELECT to_date FROM vars) as date) 

Or even less wordy:

#standardSQL -- Set your variables here WITH vars AS (   SELECT DATE '2018-01-01' as from_date,          DATE '2018-05-01' as to_date ) -- Then use them by pulling from vars with a SELECT clause SELECT * FROM your_table, vars  WHERE date_column BETWEEN from_date AND to_date