Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
I think you are after trunc() –  dezso Sep 8 '12 at 15:59
    
Thanks. that goes into a where clause exactly like above? –  fa1c0n3r Sep 8 '12 at 16:59

2 Answers 2

up vote 4 down vote accepted

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;"

share|improve this answer
    
Good points here - with practicalities I didn't think/wasn't aware of. –  dezso Sep 9 '12 at 8:45
    
thanks. I actually used the mirror image of this solution because in my problem, the timestamp column is the one that will be searched many times over, so I first converted the date format into timestamp, created your same +1 variable, and then did the where <, where > inequality to make sure that the two dates are from 0:00 to 1 day later. –  fa1c0n3r Sep 9 '12 at 13:39
    
by the way, is this really just to please the compiler? do you think there will actually be a speed advantage to defining the extra variable...? –  fa1c0n3r Sep 9 '12 at 13:41
1  
I'm not really sure. If the SQL execution plans of both scenarios are the same or performance is comparable, not using the intermediate variables should be OK. It just surprised me that incrementing to the next day with a simple "+ 1" would cause the PLW-07204 warning. But I guess since "1" is of course not a TIMESTAMP type, implict type conversion is going on at some level, like how these return different types: select DUMP(CURRENT_TIMESTAMP), DUMP(CURRENT_TIMESTAMP + 1) from dual. –  George3 Sep 9 '12 at 20:32

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.

share|improve this answer
1  
What if "inputDate" also includes time? - so both sides have to use trunc()? Or if there's an index on "inputDate" BETWEEN or >= and < should be used. Try this in SQLFiddle: SELECT * FROM test WHERE trunc(col) = trunc(CURRENT_TIMESTAMP); –  George3 Sep 9 '12 at 3:14
    
i tried WHERE inputDate = trunc(myDatabaseTimeStamp); and got PLW-07204: conversion away from column type may result in sub-optimal query plan. is there a more efficient way to do it? I need to run this query on a table with millions of rows... –  fa1c0n3r Sep 9 '12 at 3:21
    
To prevent the PLW-07204 warning, convert myDatabaseTimeStamp before using it in the SQL statement: 1. Before the BEGIN section create: myDatabase DATE; 2. Right before the SQL do: myDatabase := cast(myDatabaseTimeStamp as DATE); 3. TRUNC() is only defaulting the time to midnight. Even though "inputDate" is a DATE type it could contain time. –  George3 Sep 9 '12 at 4:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.