sql query variable not exactly unix

Shell Programming and Scripting
1

I know htis isnt exactly unix.... but hopefully someone can help me or direct me someplace to get help.

I can run sql queries in scripts against my informix db using:

dbaccess mydb myquery.sql >> sql.output

I need to write my script to select based on todays date. Its very simple.

select * from price-file
where date between yesterday and today

I want yesterday and today to be a variable always set to system date and system date - 1

I know how to get the date in a unix script. How do I pass it to the sql and have it pick it up?

Newbie to sql..

thanks

2

And i posted this in the wrong forum. i clicked on scripting and when i submitted it ended up here. :confused:

3

(I moved the thread into the correct forum.)

One solution is my datecalc script.

Other solutions have been posted from time to time as well. But we seem to have a database error that has rendered our search function into an interesting challenge.

4 
todays_date=`date +%m/%d/%C%y` #mm/dd/yyyy format
yesterdays_date=`TZ=MST31 date +%m/%d/%C%y`
echo "select * from price-file
      where date between '$yesterdays_date'
      and '$todays_date';" | dbaccess mydb >> sql.output

Embedding the SQL statement in a Unix Shell Script is one solution. The trickiest part is accurately calculating yesterday's date. As suggested, search the forum for more detail on this subject.

5

Check out this thread

Hope it helps !!

~MK

6

Best solution would be to use the date functions of sql. The informix sql may differ slightly, but in Oracle:

where date between sysdate - 1 and sysdate

which represents the last 24 hours, to this second.