Wednesday, July 10, 2013

Oracle Date and Time comparison

There are several ways to do date and time comparison in Oracle.
Here are a couple of examples how to do it (examples are using v$sqlarea view last_active_time column which data type is DATE) :

1. Comparison as date with using TO_DATE function:

SQL> select count(*) from v$sqlarea sqlst where sqlst.last_active_time > to_date('07/10/2013 13:44:10', 'mm/dd/yyyy hh24:mi:ss' );

  COUNT(*)
----------
         7

2. Comparison as string with using TO_CHAR function (Note different comparison >=  ): 

SQL> select count(*) from v$sqlarea sqlst where TO_CHAR(sqlst.last_active_time, 'YYYYMMDD-HH24MISS') >= '20130710-134410';

  COUNT(*)
----------
         8

No comments:

Post a Comment