Sasa Mihajlovic wrote:
I would like to write a Firebird SP that will insert system date and time into 2 fields of changed record into some table. What is syntax for this SP?
Helen Borrie answers:
You use context variables:
CURRENT_DATE CURRENT_TIME
and the date literal 'NOW'
CURRENT_TIME will return the same time for all records affected by a single execution of your statement.
If you have multiple records affected by a single statement execution and you need to have the exact times you can use a complicated EXTRACT expression operating on (CAST ('NOW' AS TIMESTAMP)) to get HOURs, MINUTEs and SECONDs of time. From Fb 2 onward you can also get MILLISECOND, as follows:
AS ... declare TimeVar TIME; declare TimeNow TIMESTAMP; ... BEGIN ... TimeNow = CAST ('NOW' AS TIMESTAMP); TimeVar = CAST ( ('' || EXTRACT (HOUR from TimeNow) || ':' || EXTRACT (MINUTE from TimeNow) || ':' || EXTRACT (SECOND from TimeNow) || '.' || EXTRACT (MILLISECOND from TimeNow) ) as TIME);
It would be so much easier to store just one field, a timestamp, which could be CURRENT_TIMESTAMP or CAST('NOW' AS TIMESTAMP), according to requirements...