Home » SQL & PL/SQL » SQL & PL/SQL » how to return all records i updated today?
how to return all records i updated today? [message #207667] Wed, 06 December 2006 09:09 Go to next message
jonathan184
Messages: 21
Registered: November 2006
Junior Member

Hi I am trying to return all records i updated today.
The records updated i did update mutliple tables to let you know.

this is what i sued but i get no records.
I guess i must be doing it wrong.


select * from site s, address a, contactphone cp
  where s.i_site_id = a.i_site_id and  
  s.i_customer_id = cp.i_contact_id and cp.lastupdateddate = trunc(sysdate)



I guess this simple i am new to this and still learning.
All you help wil be appreciated.

thanks
Re: how to return all records i updated today? [message #207668 is a reply to message #207667] Wed, 06 December 2006 09:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If cp.lastupdateddate contains time information, you'll need to TRUNC that as well in your select.

MHE
Re: how to return all records i updated today? [message #207669 is a reply to message #207668] Wed, 06 December 2006 09:12 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
or cp.lastupdateddate >= trunc(sysdate)

[Updated on: Wed, 06 December 2006 09:13]

Report message to a moderator

Re: how to return all records i updated today? [message #207670 is a reply to message #207667] Wed, 06 December 2006 09:15 Go to previous messageGo to next message
jonathan184
Messages: 21
Registered: November 2006
Junior Member

Yes they all have the lastupdated date in all tables.

wow you are right i did this and it worked.

select * from site s, address a, contactphone cp
  where s.i_site_id = a.i_site_id and  
  s.i_customer_id = cp.i_contact_id and trunc(cp.lastupdateddate) = trunc(sysdate);
Re: how to return all records i updated today? [message #207671 is a reply to message #207667] Wed, 06 December 2006 09:16 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
It's in the trunc around the sysdate. Trunc removes the time part of the date field. And since the last_update_date is probably a date field too, and you're not truncing that, the two are never the same. Try the following:

select <>
from your_table t
where trunc(t.last_update_date) = trunc(sysdate)



[Edit: too slow, too late...]

[Updated on: Wed, 06 December 2006 09:16]

Report message to a moderator

Re: how to return all records i updated today? [message #207672 is a reply to message #207667] Wed, 06 December 2006 09:19 Go to previous messageGo to next message
jonathan184
Messages: 21
Registered: November 2006
Junior Member

you guys are right i understand now

Because i did not trunc the column it was looking for the current time , is that why everything was blank because nothing was updated at that current time?
Re: how to return all records i updated today? [message #207675 is a reply to message #207667] Wed, 06 December 2006 09:27 Go to previous messageGo to next message
jonathan184
Messages: 21
Registered: November 2006
Junior Member

Everything worked fine thanks for all your help people.
Re: how to return all records i updated today? [message #207676 is a reply to message #207672] Wed, 06 December 2006 09:29 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
TRUNC(date_column) will set the time portion of the date to 00:00:00. When you evaluate dates, Oracle will also look at the time portion. You had truncated sysdate, so you got December 06th 2006 at midnight (00:00:00). In order to discard the time portion completely in your comparison you needed to set the time portion on the other side of your equation to 00:00:00 as well.

MHE
Previous Topic: Find & Replace
Next Topic: sql/plsql
Goto Forum:
  


Current Time: Thu Dec 08 08:39:02 CST 2016

Total time taken to generate the page: 0.05287 seconds