trunc

This was tested on Oracle 11.2. Date columns record hours, minutes and seconds in addition to the day, month and year. This is not obvious if you do not ask for the hours, minutes and seconds to be displayed:

SQL> alter session set nls_date_format = 'DD-MON-YYYY'

  2  /

Session altered.

SQL> select sysdate from dual

  2  /

SYSDATE

-----------

26-JUL-2012

SQL>

So if you see that 1 or more tables have been analyzed today:

SQL> select max(last_analyzed) analyzed

  2  from dba_tables

  3  /

ANALYZED

-----------

26-JUL-2012

SQL>

... you cannot count them like this as the hours, minutes and seconds will not match:

SQL> select count(*) from dba_tables

  2  where last_analyzed = sysdate

  3  /

  COUNT(*)

----------

         0

SQL>

You have to use the trunc function to discard the hours, minutes and seconds as follows before doing the comparison:

SQL> select count(*) from dba_tables

  2  where trunc(last_analyzed) = trunc(sysdate)

  3  /

  COUNT(*)

----------

         6

SQL>

By adding a format model to the trunc function, you can get various other dates. Today's date is the default:

SQL> select trunc(sysdate) today from dual

  2  /

TODAY

-----------

26-JUL-2012

SQL> host date

Thursday, 26 July 2012 17:22:52 BST

SQL>

This gives you the first day of the week:

SQL> select trunc(sysdate,'day') last_monday from dual

  2  /

LAST_MONDAY

-----------

23-JUL-2012

SQL>

This gives you the first day of the month:

SQL> select trunc(sysdate,'month') first_of_month from dual

  2  /

FIRST_OF_MONTH

--------------

01-JUL-2012

SQL>

This gives you the first day of the year:

SQL> select trunc(sysdate,'year') start_of_year from dual

  2  /

START_OF_YEAR

-------------

01-JAN-2012

SQL>

... and you can get the start of the century like this:

SQL> select trunc(sysdate,'cc') start_of_century from dual

  2  /

START_OF_CENTURY

----------------

01-JAN-2001

SQL>

There are other formats you can use and different ways to request the formats shown above. See the Oracle documentation for details. If you use an invalid format, you get an ORA-01821:

SQL> select trunc(sysdate,'blah') from dual

  2  /

select trunc(sysdate,'blah') from dual

                     *

ERROR at line 1:

ORA-01821: date format not recognized

SQL>