first_day / last_day with Oracle database

Posted: November 4, 2013 in Oracle, SQL

Due to some Oracle developpers’ mind, last_day() function exists while first_day() doesn’t, so strange!

To do so, the tip is to use trunc() function such as:

trunc(DATE_COLUMN, 'MM')
itrunc() has 2 signatures as mentioned on PSOUG.org site, I use the signature with a date, the second argument must be the awaited format.

Here is a full example I used, PKG_CTX_CONNEXION.getActualDate() gives the chosen date, so, to get the lines of a planning based on this date, here is the code I used for the associated view:

select *
  from T_PLANNING_WEEK plw
 where
    -- kind of first_day() synonym!
    plw.DATE_FROM >= trunc(PKG_CTX_CONNEXION.getActualDate(), 'MM')
    -- real last_day() function use
    and plw.DATE_TO <= last_day(PKG_CTX_CONNEXION.getActualDate());
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s