Comparing date and timestamp variables in a where clause in a procedure

Question

I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing the stored procedure).

I would like to do something like this: (pseudo code)

select * from MYDATABASE where inputDate = extract(date from myDatabaseTimeStamp);

but I get a compile error when I try this. Any idea what is a good actual code solution for this?

Answer 1

Assuming the DATE from user interface can contain times besides 00:00:00 (midnight) and that you only want to know if the DATE from the user interface and the TIMESTAMP fall on the same day (not same hour or minute or ?), try this:

...

myDatabaseDate    DATE;
myDatabaseNextDay DATE;

BEGIN

  -- Change TIMESTAMP to a DATE type with CAST, then TRUNCate time to 00:00:00.
  myDatabaseDate    := TRUNC( CAST(myDatabaseTimeStamp as DATE) );
  myDatabaseNextDay := myDatabaseDate + 1;

  SELECT something into some_var
  FROM MYDATABASE 
  WHERE inputDate < myDatabaseNextDay
  AND inputDate >= myDatabaseDate; 

  ... 

Note 1: Because "inputDate" is a database column and could be indexed, we don't want to do TRUNC(inputDate) because then the index won't be used.

Note 2: Doing the CAST before the SQL statement prevents this PL/SQL compilation warning: PLW-07204: conversion away from column type may result in sub-optimal query plan ... Edit: After testing this on 10.2 XE, even the "myDatabaseDate + 1" causes the warning so, although it seems like doing too much to please the compiler, I added "myDatabaseNextDay := myDatabaseDate + 1;"

Answer 2

You can use trunc():

SELECT * 
FROM my_table
WHERE trunc(date_col) = to_date('2012-09-08', 'YYYY-MM-DD')
;

or, with your example:

SELECT * 
FROM mydatabase 
WHERE inputDate = trunc(myDatabaseTimeStamp);

See a working example on SQLFiddle, and the documentation.