OraFAQ Forum: SQL & PL/SQL » how to query using sysdate 13 hours ago

Home » SQL & PL/SQL » SQL & PL/SQL » how to query using sysdate 13 hours ago

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

how to query using sysdate 13 hours ago [message #200860]

Wed, 01 November 2006 12:36

jonathan184
Messages: 21
Registered: November 2006

Junior Member

How do i query and get results like 13 hrs ago.

The query below gives 13 days, what would give me 13 hours?

select * from site s
where trim(s.lastupdateddate) = trim(sysdate-13)
order by s.lastupdateddate

If anyone could help me please reply.
Razz

Report message to a moderator

Re: how to query using sysdate 13 hours ago [message #200862 is a reply to message #200860]

Wed, 01 November 2006 12:40

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

13 hours = 13/24 of a day, so:

select * from site s
where s.lastupdateddate = sysdate-(13/24)
order by s.lastupdateddate

Not sure what the point of the TRIMS is. TRIM is for character strings, not dates.

[Updated on: Wed, 01 November 2006 12:41]

Report message to a moderator

Re: how to query using sysdate 13 hours ago [message #200879 is a reply to message #200860]

Wed, 01 November 2006 14:32

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Do you mean you want records from within the last 13 hours or only 13 hours ago? If the latter is the case then there's a good chance you'll never get any records no matter when you run this as it will only get records that are exactly 13 hours ago, to the second.

There would also be no reason to use an order by clause on the column since they would all be the same.

[Updated on: Wed, 01 November 2006 14:32]

Report message to a moderator

Re: how to query using sysdate 13 hours ago [message #200887 is a reply to message #200879]

Wed, 01 November 2006 15:19

jonathan184
Messages: 21
Registered: November 2006

Junior Member

well I just wanted to know the format.
The query i gave was a test query after playing ar ound i came up with the final one:

 select count(*) from site s where s.lastupdateddate
 between to_date(sysdate-(2/24)) and (sysdate-(1.5/24))

Report message to a moderator

Re: how to query using sysdate 13 hours ago [message #200888 is a reply to message #200862]

Wed, 01 November 2006 15:21

jonathan184
Messages: 21
Registered: November 2006

Junior Member

Thank you so much, I got it to work with the format you gave me.

I created this as the final query

 select count(*) from site s where s.lastupdateddate
 between to_date(sysdate-(2/24)) and (sysdate-(1.5/24))

Thanks for all your help i really appreciate it. Cool

Report message to a moderator

Previous Topic:	Unix and Oracle database
Next Topic:	printf-line add-on

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 20 00:50:53 CDT 2025