SQL & PL/SQL

EDIT

actual data will be written at current/actual time, and there is no data after that!
What I am trying to do, is to create a demo of "today" from the data in the past.
that is why the constraint for "today" is defined explicitly.

SO I think I have found the answer to my problem.

I am not going to delete the question below, maybe others can have also learn from it.
----------------------------

Hello,

supposed I have a table "*t*", consists of 2 column: "*Name*" (string) and "*fromdate*" (Date-Time).
TABLE T:
Name | fromdate
Duck | 2012-07-06 01:00:00
Donald | 2012-07-06 01:14:00
Mouse | 2012-07-06 02:33:00
Mickey | 2012-07-06 02:40:00
Donald | 2012-07-06 04:14:00
Mouse | 2012-07-06 05:33:00
Donald | 2012-07-06 06:14:00
Mickey | 2012-07-06 06:40:00
Mouse | 2012-07-06 07:33:00
Mickey | 2012-07-06 07:40:00
Donald | 2012-07-06 08:14:00
Mouse | 2012-07-06 09:33:00
...
...
...
Daisy | 2012-07-06 *23:30:00* --> end of 2012-07-06
Mouse | 2012-07-07 02:33:00
Mickey | 2012-07-07 02:40:00
Donald | 2012-07-07 04:14:00
Mouse | 2012-07-07 05:33:00
....
...

I use this query to extract names for today (from 00:00 today, until today on actual time, i.e: 13:00):

select name from t
where
*fromdate >= trunc(current_date - $P{val}) and*
*fromdate <= (current_date - $P{val})*



I use trunc() to get the time at "00:00:00", so I can compare the date only.
*$P{val}* is a value of "days backwards" where I can vary, if the actual day doesn't have any record on table "t", i.e: I can put 100, for 100 days in the past (for demo purposes).

Example:

select name from t
where
fromdate >= trunc(current_date - 0) and
fromdate <= (current_date - 0)

Output:
Name | fromdate
Duck | 2012-07-06 01:00:00
Donald | 2012-07-06 01:14:00
Mouse | 2012-07-06 02:33:00
Mickey | 2012-07-06 02:40:00
...
...
Daisy | 2012-07-06 *14:20:00* --> current date, current time, output ends here.


The similar constraint for *"yesterday"*:
*trunc(b.fromdate)= trunc(current_date - $P{val})*

Example:
select name from t
where
trunc(b.fromdate)= trunc(current_date - 100)
Here we compare the date on the same time (00:00:00)

Output:
Name | fromdate
Duck | 2012-07-06 01:00:00
Donald | 2012-07-06 01:14:00
Mouse | 2012-07-06 02:33:00
Mickey | 2012-07-06 02:40:00
Donald | 2012-07-06 04:14:00
Mouse | 2012-07-06 05:33:00
...
...
Daisy | 2012-07-06 *23:30:00* --> output ends here: before 24:00:00 of that day

QUESTION:
this *'$P{val}'* comes from a list of values.
The user has an combo box interface, and they choose one of the values from html combo box (I cannot modify the code/any class), for example:
* Today --> $P{val} = 0
* Yesterday --> $P{val} = 1
* 30 days ago --> $P{val} = 30

as you can see, the query constraint for "today" (00:00 - actual time) and "yesterday" (a single day) is different.
means:
If I want to query the names for "today, I cannot put '0' into query of "yesterday", because it will show all names in "one day", but what I want is "from 00:00 - actual time" and vice versa.
BUT, the *'$P{val}'* only available for one query only; either "today" or "yesterday".

Any body has better sql query, so that the query for "today" and "yesterday/any random day" can be merged in one single query ?
(a single query for all *'$P{val}'*

Because, on the platform I am working on, *there is only 1 place available for 1 single query.*
so if the user choose "today" ($P{val} = 0), the available SQL query is only for "today":
*fromdate >= trunc(current_date - $P{val}) and*
*fromdate <= (current_date - $P{val})*

and if user choose "yesterday" ($P{val} = 1), the query statement gives only output from "yesterday", BUT it has the same time range of the query statement for "today"; yesterday 00:00 - yesterday at "current-time of today"


---------
ORA 11g

Edited by: user1142502 on 06.07.2012 05:39