In the beginning.... FIRST_DAY
Today I got a question from a co-worker originating from a DB2 DBA:
"DB2 has a LAST_DAY() function which returns the last day of the month given a date or timestamp.
But where is the matching FIRST_DAY() function?"
Good question! First let me explain that LAST_DAY() was introduced to simplify enabling Oracle applications to DB2. It is a mere convenience function since simple date arithmetic will produce it. Since no other vendor has FIRST_DAY() and that function is equally trivially produced there simply was no need to add it.
But this takes me back to my student days where our math professor boldly wrote some equation onto the chalk board.
He proclaimed the equation to be trivially true.
Then he stepped back, squinted and left the room.
A few minutes later, everyone more or less patiently waiting, he re-entered and said: "Yeah, it really is trivial".
So I do claim FIRST_DAY() is trivial and here is proof:
CREATE OR REPLACE FUNCTION FIRST_DAY(arg DATE) RETURNS DATE NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC RETURN arg - (DAY(arg) - 1) DAYS;
For good measure let's add the TIMESTAMP version as well:
CREATE OR REPLACE FUNCTION FIRST_DAY(arg TIMESTAMP) RETURNS TIMESTAMP NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC RETURN arg - (DAY(arg) - 1) DAYS;