You need to use the special hiveconf for variable substitution. e.g.
hive> set CURRENT_DATE='2012-09-16';
hive> select * from foo where day >= ${hiveconf:CURRENT_DATE}
similarly, you could pass on the command line:
% hive -hiveconf CURRENT_DATE='2012-09-16' -f test.hql
Note that there are env and system variables as well, so you can reference ${env: USER} for example.
To see all the available variables, from the command line, run
% hive -e 'set;'
or from the hive prompt, run
hive> set;
Update: I've started to use hivevar variables as well, putting them into hql snippets I can include from hive CLI using the source command (or pass as -i option from the command line). The benefit here is that the variable can then be used with or without the hivevar prefix, and allow something akin to global vs local use.
So, assume have some setup.hql which sets a tablename variable:
set hivevar:tablename=mytable;
then, I can bring into hive:
hive> source /path/to/setup.hql;
and use in a query:
hive> select * from ${tablename}
or
hive> select * from ${hivevar:tablename}
I could also set a "local" tablename, which would affect the use of ${tablename}, but not ${hivevar:tablename}
hive> set tablename=newtable;
hive> select * from ${tablename} -- uses 'newtable'
vs
hive> select * from ${hivevar:tablename} -- still uses the original 'mytable'
Probably doesn't mean too much from the CLI, but can have hql in a file that uses source, but sets some of the variables "locally" to use in the rest of the script.