I want to get date value range is 08/28/2011 12:00:00 AM.
that is *not* a range, that is a very specific point in time.
I'm not sure what you are trying to ask with this:
where Entry_date between 08/28/2011
12:00:00 AM and 09/04/2011 12:00:00 AM. Please tell me which function give me
this exact date value. Since the date value is not manualually entered.
You wrote this on sep 8th, you want aug-28 midnight through sep-4 midnight (mon through sun) - so I guess you want to take the current date and find the previous sunday and then subtract a week and get all in between. If I guessed right, I not only win a prize but I can answer your question.
ops$tkyte%ORA11GR2> select
2 next_day( trunc(sysdate,'dy')-7, 'sun' ) -6,
3 next_day( trunc(sysdate,'dy')-7, 'sun' ) +1-1/24/60/60
4 from dual
5 /
NEXT_DAY(TRUNC(SYSDA NEXT_DAY(TRUNC(SYSDA
-------------------- --------------------
29-aug-2011 00:00:00 04-sep-2011 23:59:59
test that to make sure it gets the right sunday as far as you are concerned when executed on sunday!
you want all of the rows where the dates are between those two values I believe.
If you do not spell sunday SUN, or you want to be safe, you can use:
ops$tkyte%ORA11GR2> select
2 next_day( trunc(sysdate,'dy')-7, to_char( to_date('01/07/1900','dd/mm/yyyy'), 'dy' ))-6,
3 next_day( trunc(sysdate,'dy')-7, to_char( to_date('01/07/1900','dd/mm/yyyy'), 'dy' )) +1-1/24/60/60
4 from dual
5 /
NEXT_DAY(TRUNC(SYSDA NEXT_DAY(TRUNC(SYSDA
-------------------- --------------------
29-aug-2011 00:00:00 04-sep-2011 23:59:59