Shell Programming and Scripting

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

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

(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.

Code:

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.

Check out this thread


Hope it helps !!

~MK

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

Code:

where date between sysdate - 1 and sysdate

which represents the last 24 hours, to this second.