Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012

    Question Unanswered: Execute function to get the current date

    Hi.. first post...

    I want to do something very simple... when a record is inserted into a table, or modified, I want to set a date field in the record to the current date. I know I can do that by creating a function that simply returns the current date and then using..

    (execute function MyGetCurDate() into datetest.date1);

    But as Infx has date functions already I thought... why write my own? Surely from my trigger I can do something like...
    • (execute function CURRENT() into datetest.date1);

    • (execute function TODAY() into datetest.date1);

    Or (this is the one I think should work)...
    • (execute function DATE(TODAY) into datetest.date1);

    But no luck...

    However this does work (or at least the trigger compiles)
    • (execute function LAST_DAY(TODAY,'Mon') into datetest.date1);

    ... although it does not do what I want it to do.

    This also seems to work (or again it compiles... I have not tested it)
    • (update datetest set date1 = TODAY where id =;

    But that is horrible! ... and I am not sure it will actually work... I would rather write a function.

    I see from Time Functions that the likes of DATE is defined as a function.

    So am I trying to to the impossible here?

    Anyone had to do this before?

    (I'm on Infx 11.5 BTW)

  2. #2
    Join Date
    Dec 2012
    This might make it clear... this works... but requires that I define a dumb function that calls a function...

    drop table datetest;
    create table datetest (id serial, date1 date, date2 date, valueX int) ;
    drop function getcurdate;
    create function getcurdate() returning date
    end function
    create trigger datetest_change update on datetest
            referencing old as old new as new
            for each row
            when ((old.valueX != new.valueX ) )
            (execute function getcurdate() into datetest.date1);
    insert into datetest values (0,'1/1/2000','1/1/2000',10);
    update datetest set valuex = valuex +1;
    select date1 from datetest;

Tags for this Thread

Posting Permissions

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