OraFAQ Forum: SQL & PL/SQL » condition based on datetime

Home » SQL & PL/SQL » SQL & PL/SQL » condition based on datetime (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

condition based on datetime [message #401518]

Tue, 05 May 2009 06:10

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

Report message to a moderator

Re: condition based on datetime [message #401521 is a reply to message #401518]

Tue, 05 May 2009 06:13

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: condition based on datetime [message #401536 is a reply to message #401521]

Tue, 05 May 2009 07:55

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

Report message to a moderator

Re: condition based on datetime [message #401537 is a reply to message #401536]

Tue, 05 May 2009 07:58

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Did you read the link I posted?

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

Regards
Michel

Report message to a moderator

Re: condition based on datetime [message #401540 is a reply to message #401536]

Tue, 05 May 2009 08:15

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.

Report message to a moderator

Re: condition based on datetime [message #401546 is a reply to message #401540]

Tue, 05 May 2009 08:24

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

Re: condition based on datetime [message #401565 is a reply to message #401559]

Tue, 05 May 2009 10:08

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	SQL Query to get all constraints of database
Next Topic:	virtual table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 18 09:09:56 CDT 2025