Home » SQL & PL/SQL » SQL & PL/SQL » DateTime Function (Oracle 8i)
DateTime Function [message #359302] Fri, 14 November 2008 11:14 Go to next message
shanks_uk
Messages: 3
Registered: November 2008
Junior Member
i'm looking for a SQL Statement which retrieves all the data after 06:00 (AM )

Is following Queries is right ?

select count(*) from TestHIS where hisDATE > to_date(to_char(sysdate,'DDMONYYYY')||'06:00','DDMONYYYYHH:MI')

It looks ok to me. But we I have lot of records in the table so comparing it will difficult isn’t?

the above query returns me : 127 records

but when i execute this query

select count(*) from TestHIS where hisDATE> = sysdate

its returns me : 6 records

Is any another way to write the query or best one?

[Updated on: Fri, 14 November 2008 11:18]

Report message to a moderator

Re: DateTime Function [message #359304 is a reply to message #359302] Fri, 14 November 2008 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i'm looking for a SQL Statement which retrieves all the data after 06:00 (AM )

6AM of which date?

Regards
Michel
Re: DateTime Function [message #359305 is a reply to message #359304] Fri, 14 November 2008 11:18 Go to previous messageGo to next message
shanks_uk
Messages: 3
Registered: November 2008
Junior Member
His_date
Re: DateTime Function [message #359307 is a reply to message #359305] Fri, 14 November 2008 11:35 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@shanks_uk,

Please read OraFAQ Forum Guide especially on "How to Format your Post?"

Post a test case (create table statements with insert statements of some dummy records) along with your desired results. It will help others understand your requirement much better resulting in more accurate and quick responses for your post.


Regards,
Jo
Re: DateTime Function [message #359310 is a reply to message #359305] Fri, 14 November 2008 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trunc(hisdate) is hisdate at 0:00
There are 24 hours per day, so 6/24 is 6 hours.

Regards
Michel
Re: DateTime Function [message #359576 is a reply to message #359310] Mon, 17 November 2008 06:32 Go to previous messageGo to next message
shanks_uk
Messages: 3
Registered: November 2008
Junior Member
thanks micheal

this script worked for me

SELECT count(*)
FROM
HISTORY
WHERE
HIS_DATE >
TRUNC(sysdate) + 360/1440
Re: DateTime Function [message #359584 is a reply to message #359576] Mon, 17 November 2008 07:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you find more intuitive to represent 6 hours:
6/24 or 360/1440 ??
Re: DateTime Function [message #359588 is a reply to message #359584] Mon, 17 November 2008 07:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I like to use expressions like this for ease of understanding:
select trunc(sysdate) + power(sin(atan(1)),4) from dual;


It helps winnow out those who weren't paying attention in Maths.
Previous Topic: null in oracle
Next Topic: Rebuild index
Goto Forum:
  


Current Time: Thu Dec 08 12:14:46 CST 2016

Total time taken to generate the page: 0.11422 seconds