Home » SQL & PL/SQL » SQL & PL/SQL » condition based on datetime (Oracle 11g)
condition based on datetime [message #401518] Tue, 05 May 2009 06:10 Go to next message
ayaz_ncr
Messages: 8
Registered: May 2009
Junior Member
Hi,

A query will be executed everyday at 9 am in the morning. However the query has to display all the records between 11PM to 2 AM the previous night.

So I need to add a condition in the WHERE clause of a SQL query to limit the number of records in such a way that it takes only those records where RequestDate(a date column in oracle table) is between 11PM to 2AM the previous night.

i.e:

RequestDate between 11PM (prev night) and 2AM (today morning)

How the condition can be constructed by taking time into consideration?

Thanks in advance,
Ayaz
Re: condition based on datetime [message #401521 is a reply to message #401518] Tue, 05 May 2009 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should post what already tried and prove us you have worked on the subject and are not just waiting for us to make your job.

And, as for your previous post, you have to post a WORKING test case...

Hint: have a look at SQL Reference, Chapter 5 Functions, Datetime Functions section.

Regards
Michel
Re: condition based on datetime [message #401536 is a reply to message #401521] Tue, 05 May 2009 07:55 Go to previous messageGo to next message
ayaz_ncr
Messages: 8
Registered: May 2009
Junior Member
This is what I was trying:

SELECT TO_CHAR(SYSDATE-1,'DD-MON-YY HH24:MI:SS') FROM DUAL

returns 04-MAY-09 20:49:55

I need to change the SQL to return: 04-MAY-09 23:00:00 (always 11PM of previous day no matter what current time is)

hope this helps,
thanks
Ayaz
Re: condition based on datetime [message #401537 is a reply to message #401536] Tue, 05 May 2009 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the link I posted?

Clues:
- trunc (read about this function)
- 1/24 is one hour

Regards
Michel
Re: condition based on datetime [message #401540 is a reply to message #401536] Tue, 05 May 2009 08:15 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
why not the below ?..

SQL>  select to_char(sysdate-1) ||' 23:00:00' from dual;

TO_CHAR(SYSDATE-1)
------------------
04-MAY-09 23:00:00


since 23:00:00 is fixed.
Re: condition based on datetime [message #401546 is a reply to message #401540] Tue, 05 May 2009 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not? Because it is wrong:
SQL> select to_char(sysdate-1) ||' 23:00:00' from dual;
TO_CHAR(SYSDATE-1)||'23:00:0
----------------------------
04/05/2009 15:23:25 23:00:00

1 row selected.

Read and follow the OraFAQ Forum Guide which stated to post clues or point to the right direction and not full solution.

Regards
Michel

[Updated on: Tue, 05 May 2009 08:27]

Report message to a moderator

Re: condition based on datetime [message #401559 is a reply to message #401546] Tue, 05 May 2009 09:31 Go to previous messageGo to next message
ayaz_ncr
Messages: 8
Registered: May 2009
Junior Member
Although I understand your point, I am not getting the result you got:

select to_char(sysdate-1) ||' 23:00:00' from dual;
-------------------------------------------------
04-MAY-09 23:00:00


By the way thanks for the hint earlier. I got the desired condition as follows:

RequestedDate BETWEEN TRUNC(SYSDATE)-1/24 AND TRUNC(SYSDATE)+2/24

Thanks & Regards
Ayaz
Re: condition based on datetime [message #401565 is a reply to message #401559] Tue, 05 May 2009 10:08 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Although I understand your point, I am not getting the result you got:

The result relies on implicit format which is not the same for every one. So the query is wrong.

Regards
Michel
Previous Topic: SQL Query to get all constraints of database
Next Topic: virtual table
Goto Forum:
  


Current Time: Fri Dec 09 15:47:33 CST 2016

Total time taken to generate the page: 0.10032 seconds