Compare date from db2 table to yesterday's Unix system date

Shell Programming and Scripting
1

I am currently running the following Korn shell script which works fine:

#!/usr/bin/ksh
count=`db2 -x "select count(*) from schema.tablename"`
echo "count"

I would like to add a "where" clause to the 2nd line that would allow me to get a record count of all the records from schema.tablename where PROCESS_DT(a datetime variable in tablename which is formatted as DATE9.) is equal to the today's unix system date - 1. I'm not sure of the proper syntax. Please help.

count=`db2 -x "select count(*) from schema.tablename where PROCESS_DT=UNIX system date-1"`

2

A link that may helps you:

Regards

3

I now know how to find yesterdays date, however, I get an error message stating that the operands (PROCESS_DT and DATE_STAMP) are not compatible. PROCESS_DT is a DB2 date time variable with a DATE9 format. How can I declare DATE_STAMP to be a date time variable?

#!/usr/bin/ksh
DATE_STAMP=`TZ=CST+24 date +%y%m%d`
count=`db2 -x "select count(*) from schema.tablename where PROCESS_DT="$DATE_STAMP""`
echo "$count"

4

I'm not familiar with db2 but you should do something like:

DATE_STAMP=`TZ=CST+24 date +%Y-%m-%d`
count=`db2 -x "select count(*) from schema.tablename where PROCESS_DT=DATE("$DATE_STAMP")`

I changed the date format and in the sql statement I convert the string in a date value.

Regards

5

Franklin52,

After trying your suggestion, I got the following error:

SQL0183N A datetime arithmetic operation or a datetime scalar function has a result that is not within the valid range of dates. SQLSTATE=22008

6

Sorry, I forgot to change the date format before running again. It worked, but returned a zero value for count. I'm trying to figure out why that happened when PROCESS_DT in the db2 table is equal to yesterday's date.

7

I just tried changing the format of DATE_STAMP to +%m/%d/%Y to match the format of the values of PROCESS_DT (04/22/2008), but still got the same error as listed earlier.

8

As earlier mentioned, I'm not familiar with this stuff, I hope a db2 expert comes along with the right answer but in the meantime you may look at this link:

DB2 Basics: Fun with Dates and Times

Regards

9

Try this one..

DATE_STAMP=`TZ=CST+24 date +%Y-%m-%d`
count=`db2 -x "select count(*) from schema.tablename where PROCESS_DT= TO_DATE($DATE_STAMP,'YYYY-MM-DD')`

The above date comparision is working in Oracle.

10

try using this format. This is the DB2 timestamp format.

YYYY-mm-dd-HH.MM.SS.ssssss

+%Y-%m-%d-00.00.00.000000