Home » SQL & PL/SQL » SQL & PL/SQL » Time condtion using Oracle
Time condtion using Oracle [message #245347] Sat, 16 June 2007 04:35 Go to next message
patneel
Messages: 44
Registered: June 2007
Location: India
Member
Hi,

i wants to build below condition using ORACLE


Here is what is required:

Start time is between 8:01-8:15 (HH:MM) it should display 8

Start time is between 8:16-8:29 it should display 8:30

Start time is between 8:31-8:44 it should display 8:30

Start time is between 8:46-8:59 it should display 9


Start time is column from Timeperiod table in DB. Having Datatype as Date(dd/mm/yyyy HH:MM:SS AM)

Can any one please suggest logic?

Thanks

[Updated on: Sat, 16 June 2007 04:46]

Report message to a moderator

Re: Time condtion using Oracle [message #245350 is a reply to message #245347] Sat, 16 June 2007 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter session set nls_date_format='HH24:MI:SS';

Session altered.

SQL> with
  2    data as (
  3      select trunc(sysdate)+8/24+7*(rownum-1)/1440 dt from dual connect by level <= 10
  4    )
  5  select dt, 
  6         trunc(dt)+round(to_number(to_char(dt,'SSSSS'))/1800)/48 dt2
  7  from data
  8  /
DT       DT2
-------- --------
08:00:00 08:00:00
08:07:00 08:00:00
08:14:00 08:00:00
08:21:00 08:30:00
08:28:00 08:30:00
08:35:00 08:30:00
08:42:00 08:30:00
08:49:00 09:00:00
08:56:00 09:00:00
09:03:00 09:00:00

10 rows selected.

Regards
Michel
Re: Time condtion using Oracle [message #245502 is a reply to message #245347] Sun, 17 June 2007 23:22 Go to previous messageGo to next message
patneel
Messages: 44
Registered: June 2007
Location: India
Member
Thanks for Reply,

But can you help me to understand your logic ?

Why you done division by 1800 and 48 and also you selected the format sssss?

Thanks
Re: Time condtion using Oracle [message #245527 is a reply to message #245502] Mon, 18 June 2007 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format SSSSS gives you the time in seconds.
1800 seconds is 30 minutes, you want to round to each 30 minutes.
48 is the number of 1/2 hours in a day. So 1 day = 1800 * 48 seconds and 1 second = 1/1800/48 day.

Regards
Michel

[Updated on: Mon, 18 June 2007 01:11]

Report message to a moderator

Re: Time condtion using Oracle [message #245535 is a reply to message #245502] Mon, 18 June 2007 01:03 Go to previous message
patneel
Messages: 44
Registered: June 2007
Location: India
Member
Thanks...

Previous Topic: Select query fetching more than expected records
Next Topic: SQL query to reverse
Goto Forum:
  


Current Time: Sat Dec 03 22:07:33 CST 2016

Total time taken to generate the page: 0.20370 seconds