monthly tasks

sobnc · #1 (**permalink**) 12-05-09, 14:45

Hi Sir
Please I looking how to execute sql queries every end of month without modification of the query. For example every end of month i have a dozen of sql queries to execute and I should send send the result to my colleagues but I should enter in each of them to modify the periode from day 1 to day 30 if its novembre or from day 1 to day 31 if its december etc. How should i write those fields in my sql script?
I am using informix database with dbaccess data_base_name
Thanks

Tyveleyn · #2 (**permalink**) 12-06-09, 03:50

Hi, I presume your query session is started on an external trigger, like crontab. Then you can use the current date to determine the period with a clause like this:

Code:

WHERE date_column BETWEEN date(extend(today, YEAR TO MONTH))
AND date(extend(today, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY;

This clause takes the current month as period, if it should be the next month add one UNITS MONTH to both operands.

Regards,
Hans

sobnc · #3 (**permalink**) 12-07-09, 13:44

Dear Sir
Thanks for your reply but I want to ask this. Imagine that the column day in my table is named date and every last of month two or tree days after my query has one clause based on date to be modify as following
select * from my_table where (date>="01/11/2009" and date<="30/11/2009") and every beggining of month after I should modify the where clause according to the last month. How can I implement what you have send to me "WHERE date_column BETWEEN date(extend(today, YEAR TO MONTH))
AND date(extend(today, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY;
Thanks again

Tyveleyn · #4 (**permalink**) 12-07-09, 16:44

Just implement it as how it's written:

Code:

SELECT * FROM my_table
WHERE 'date' BETWEEN date(extend(today, YEAR TO MONTH))
AND date(extend(today, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY;

if the builtin function TODAY evaluates to a date in december and you want the results of december.
If in december you want the results of november write:

Code:

SELECT * FROM my_table
WHERE 'date' BETWEEN date(extend(today, YEAR TO MONTH) -1 UNITS MONTH)
AND date(extend(today, YEAR TO MONTH)) -1 UNITS DAY;

The thing with the EXTEND function is that it transforms a date(time) to a subset of years, months, days (hours, minutes, seconds, fractions) according to the specifications (in this case YEAR TO MONTH). By adding 1 month you get a valid datepart that complies with the calendar (adding 1 month to 2009-12 returns 2010-01). Casting with the enclosing DATE function adds the first day of a month to it, i.e. 2010-01-01.
For getting the last date of a given month you can do the same with first adding one month to the adjusted date, then cast it to the first day of the next month and finally subtract one day to get the last day of the month before, i.e. 2009-12-31.

Regards,
Hans

sobnc · #5 (**permalink**) 12-10-09, 02:38

Dear SIr
I am still having problem to execute the query you send me:
SELECT * FROM my_table
WHERE 'date' BETWEEN date(extend(today, YEAR TO MONTH))
AND date(extend(today, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY;
after traducing in my environment it's look like
Assume that
my_table=transaction
field date=dco

SELECT * FROM bkhis
WHERE 'dco' BETWEEN 'dco'(extend(today, YEAR TO MONTH))
AND 'dco'(extend(today, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY
after executing i have an error message "syntax error" and the cursor stop just between the second dco an (extended)
Please give a hand

Tyveleyn · #6 (**permalink**) 12-10-09, 07:36

Try this:

Code:

SELECT * FROM transaction
WHERE dco BETWEEN date(extend(today, YEAR TO MONTH))
AND date(extend(today, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY;

In my last example I put the date column reference between quotes because 'date' is also a builtin function, just like 'extend' and 'today'.

Regards,
Hans