Archive for the ‘SQL’ Category

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