date range

Applications UNIX Desktop Questions & Answers
1

I have a number of instances wher I need to run reports for the previous month and need to include the last months date range in the sql.
I want to create a string which consists of the first and last dates of last month separated with an ' and ' ie for this month (Feb) I want it to say
'01/01/10 and 31/01/10'
Needs to work for all months and work in leap years. It will be run on the same date of each month.
Any help would be appreciated
Niven

2

Oracle, sql server, db2?

3

What is the exact schema definition of the date field?

4

Thanks for your interest. At the moment I do this

MONTH=`date +%m`
case MONTH in
01) NEWDATE="01/12/09 and 31/12/09" ;;
02) NEWDATE="01/01/10 and 31/01/10" ;;
etc
*) exit 0
esac

and in the query I say

'and date_reg between $NEWDATE'

This requires the dates to be changed every year obviously which is what Im trying to address.
Its running HP-UX (not sure which version) with a Unify database. The sql is the Unify SQL. The date field is defined as 'huge date' whatever that means in Unify.
Hope this helps.
Thanks