Results 1 to 8 of 8

Thread: monthly tasks

  1. #1
    Join Date
    Dec 2009
    Posts
    28

    Post Unanswered: monthly tasks

    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

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  3. #3
    Join Date
    Dec 2009
    Posts
    28

    monthly tasks

    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

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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
    Last edited by Tyveleyn; 12-07-09 at 16:49.

  5. #5
    Join Date
    Dec 2009
    Posts
    28

    monthly tasks

    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

  6. #6
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  7. #7
    Join Date
    Dec 2009
    Posts
    28

    next step

    Dear sir
    Thanks for your reply but the row return are for the month of december and not november as i was expecting.
    Please give me again a hand

  8. #8
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    See my reply of 7 december...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •