Home » SQL & PL/SQL » SQL & PL/SQL » how to query using sysdate 13 hours ago
how to query using sysdate 13 hours ago [message #200860] Wed, 01 November 2006 12:36 Go to next message
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
Re: how to query using sysdate 13 hours ago [message #200862 is a reply to message #200860] Wed, 01 November 2006 12:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
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))
icon6.gif  Re: how to query using sysdate 13 hours ago [message #200888 is a reply to message #200862] Wed, 01 November 2006 15:21 Go to previous message
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
Previous Topic: Unix and Oracle database
Next Topic: printf-line add-on
Goto Forum:
  


Current Time: Tue Dec 06 08:47:17 CST 2016

Total time taken to generate the page: 0.05555 seconds