Hi, in my UDB 8.2 I created an UDF that represents the builtin 'CURRENT DATE' function. It has the name 'TODAY' and is nothing more than a SQL expression UDF. When run in the Development center it returns today's date. But when incorporated in a SQL statement (in the Control Center or my application) like this:
SELECT * FROM TABLENAME WHERE LASTDATE < TODAY;
SQL0206N "TODAY" is not valid in the context where it is used.
It's executed by the same user as the owner of the UDF and according to the properties in the Control Center that user has EXECUTE privileges.
What's missing here?
Hi, regarding the question above I can say that the solution is to use the UDF as 'TODAY()'. Since it's meant as a translation of the Informix builtin SQL function 'TODAY' I need to use it without the parentheses. Is that possible?
Hi, the application I'm working on is designed for usage with a Informix database and there TODAY is the builtin equivalent of DB2's CURRENT DATE. The database on my laptop I use for development is DB2 and I don't want to alter the applications code to fit the DB2 SQL syntax. A builtin function CURRENT (for representing a DATETIME value) would also be fit for my needs but I don't believe that's in DB2.
Would a Java written stored procedure or a routine in C be able to do the trick by your knowledge?
Thanks, but I thought of another similar function in both databases. For that I need to get the date of the first day of a given month in SQL. E.g. I need the date of the first day of today's month minus 5 months, what will be 2008-05-01. In Informix that's no problem for me but I'm still trying in DB2. Any suggestions in that matter are appreciated.